Free Microsoft Excel 2013 Quick Reference

Combining VLOOKUP and IF statements

Hello

I'm trying to write a formula that uses a lookup. If the result of the lookup is a "." then I want to return a 0. Otherwise I want to return whatever value is there.

Having difficulty getting this to work. Can anyone help.

Thanks


Hi,
I am looking for some help with combining VLOOKUP and IF statements.
The plan is to have a form with dependant drop down boxes that reply on data ranges however I want the data ranges to be defined in real time from another workbook. I.E. so they are not static ranges.
Does anyone have any sugestions on how this could be achieved?

Thanks in advance for your help.

Dave

I'm trying to get around the problem of VLOOKUP looking up only the first
found match. Here's and example:

Each row in the first worksheet has a unique value in the first column
(file_ID), and a variable number of the same file_ID entries in consecutive
row blocks in the second worksheet. Now, I'd like to extract to the first
worksheet the corresponding values in, let's say, column 5 in the second
worksheet IF the column 3 value in that 2nd worksheet meets a certain
condition. The values in column 3 are unique for each file_ID entry.

I couldnt solve the problem with various combinations of VLOOKUP and IF and
suspect that an array formula would be needed--but I don't know how to bite
it...

z.entropic

I have a spreadsheet which filters on various criteria based on location and contract renewal dates etc.
Previously, I had a simple if statement to filter on usage criteria: =IF(AY2>=2000, "GOOD", IF(AY2>=1000, "OK", IF(AY2>=0.1, "BAD", IF(AY2 >0.1, "BAD", "Nil usage" ) ) ) )

(not pretty but it worked)

I now find I need to create a more accurate filter based on other numerical criteria i.e. more than 500 hours and more than 5 tasks set and Data sent in would return GOOD
I attach an example of what I want with the data headings and rules:

HTML Code: 
Client Ref:	name	Data sent?	HoursQ1	Logins	Groups	Tasks	Status:
1	Brown	Y	1200	6	6	17	GOOD
2	Jones	N	200	2	8	7	BAD
3	Smith	N	75	4	2	15	BAD
4	David	Y	40	2	6	6	BAD
5	Jupp	Y	160	0	3	12	BAD
6	Fine	Y	245	9	4	14	OK
							
							
RULES:							
If Data sent is "N" then "Status" is always "BAD" regardless of the other conditions							
							
Good:			>500	=>5	=>5	=>3	
OK:			=>100	=>3	=>3	=>2	
BAD:			=<99	=<2	=<2	=<1	
I also attach an example sheet as above. I hope someone can help.

Thanks
Simon

Hello,
I will try to explain below what I am trying to accomplish. I have two spreadsheets:

Spreadsheet #1: 3 columns below
ID# Date1 Date2
1 1/1/07 1/5/07
2 2/1/07 2/5/07

Spreadsheet #2: 3 columns below
ID# Date
1 1/4/07 ...include because in date range for ID#1 in Spreadsheet #1
1 1/10/07 ...do not include because date is out of range above
2 2/3/07 ...include because in date range for ID#2 in Spreadsheet #1
2 2/20/07 ...do not include because date is out of range above

As you can hopefully see, I need to look up the ID#s from Spreadsheet #1 in Spreadsheet #2 where the dates are in the range above. Once I identify those lines in Spreadsheet #2, I want to pull over data from other fields from Spreadsheet #2 such as "Payments".

I feel like it is a vlookup and if statement, but not sure. Thanks!

Afternoon All,

I am having problems trying to combine a Vlookup and If statement.

The formula I have written is: =IF(VLOOKUP(B386,Extensions!$A$4:$D$128,3,FALSE)="","",VLOOKUP(B386,Extensions!$A$4:$D$128,3,FALSE))

The problem is that where the Vlookup does not find the entry in B386 I am getting the error "N/A" appearing. This is correct because the value in B386 (for example) does not exist in the sheet I am looking up.

What I want to happen is that rather than "N/A" being shown I would like a blank space to be entered.

Can anyone tell me how I can force a blank space (hence why have used "" within my formula) rather than the default "N/A" which the vlookup throws up?

Regs

MZP

Hello :-),

I am trying to make a dynamic spreadsheet that will:

• read what Travel Modes a user has selected (Walk, Cycle, Car, Bus, Tube, Train, and/or Aeroplane) in the spreadsheet.
• read what Metric the user has selected (CO2, duration, or distance) in the spreadsheet.
• Identify the result for each Transport Mode and the Metric (e.g. the duration of a journey by Car). This result will have already been calculated in the spreadsheet.
• Build a table of only selected Travel Modes and Metric results
• Generate a graph from the table of only the selected Travel Modes (X-axis) and the Metric (Y-axis)
I have been combining IF and HLOOKUP formulae, but my formula is too long. Excel does not allow me to use enough IF statements (Excel limits the number of IF statements to about 4).

Does anyone know a way to achieve this?

I have attached a spreadsheet with as far as I have got if that is helpful?

Thank you very much,
Casper

Hello - i tried looking over the internet but thought i might get a faster result if i asked you guys. I need this for work. I have done a million VLookups but have never used a If Statement. This is what I need. I work in a department where we inventory different trucks. We count different trucks on different days. It Looks something like this:

Truck A - 10/3
Truck B - 10/1
Truck C - 10/3
Truck D - 10/2
Truck E - 10/3
Truck F - 10/1

Each night i download the inventory value for the entire region and combine all of them in one large table like this.

10/1 Truck A - $50
10/1 Truck B - $150
10/1 Truck C - $110
10/1 Truck D - $120
10/1 Truck E - $210
10/1 Truck F - $240
10/2 Truck A - $50
10/2 Truck B - $150
10/2 Truck C - $175
10/2 Truck D - $135
10/2 Truck E - $210
10/2 Truck F - $215
10/3 Truck A - $60
10/3 Truck B - $110
10/3 Truck C - $135
10/3 Truck D - $165
10/3 Truck E - $180
10/3 Truck F - $215

I need to devise a formala to grab Truck A, it will lookup that it was counted on date 10/3 then lookup result of $60.

Can someone either type a sample formula or show me a really helpful link on the internet.

Hi All-

First post woohoo! Firstly I’m terrible with Visual Basic and have an understanding of Macro's go (as far as recording them is concerned) so the more detail the better :-). I've see a lot of posts on here but I didn’t recognize any that fit my situation, and Im hoping someone with a bit more experience could help out!

So I’m working on a Macro, and I want (unless there is an easier way) to have it loop through a set of Data (Hours Thresholds see below), and once the criteria is met have the correct If Statement popped in the cell and continue to do this until there isn't any additional Data.

I am using the Hours Thresholds as the identifiers so this would be where the Vlookup comes in to determine which If Statement should be used...Below is how they correlate to each other. Any help would be SUPER appreciated!!! I've been staring at this trying to figure it out... no such luck :c[...

Threshold If Statement
8 hours =IF(J2>0.01,IF(J20.01,IF(J20.01,IF(OR(J210),"Yes","No"),"No")
8 to 10 hours =IF(J2>0.01,IF(OR(J210),"Yes","No"),"No")
9 to 10 hours =IF(J2>0.01,IF(OR(J210),"Yes","No"),"No")

I am doing a simple vlookup, but need to search another column and if I find
a match change the lookup result.
example
vlookup(a2,tab1a2:b50,3). if A3 = 25, then I need to change the lookup value
to reflect the if statement result.
Any help would be appreciated

I am using VLOOKUP and when that function doesn't find the value in the
table, it returns "#NA" as the value. I then can't sum the column
because of the #NA in some of the cells. Is there a way that I can
return a zero or a null instead of the #NA? I was thinking that perhaps
the VLOOKUP could be combined with IF... Thanks for your help in
advance.

I'm trying to use an if statement to do a vlookup to verify data on a different worksheet and then use another if statement to perform an equation based on the verification. Here is what I have tried and I get either #NA, #Name, or #Value errors. The reason for this is the separate overtime hours from regular hours only if the person is hourly, not salary. I'm trying the formula in the Thursday worksheet in the Overtime Hours column (BC).

=if(vlookup("hourly", Employees,3,false),if(BB5>8,BB5-8),0)
=if(vlookup("hourly", Employees,3,false),if(BB5>8,BB5-8,0)
=if(vlookup(C3:C12,Employees,3,)="hourly",BB5-8,0)
=if(BB5>8(vlookup("hourly",Employees,3,false), BB5-8,0)

I've also tried all of these with "salaried" in the equation instead of "hourly". Any help would be greatly appreciated. I've been working on this for 2 days

Hi,

I am having trouble getting the syntax right for entering a count & "double if" function in excel. I am trying to create an origin / destination matrix and I have two columns of data. The 1st column contains a name code for all origins and the second contains a name code for all detinations. Basically what I am trying to do is create a combined count and if statement that reads the 1st column of data and the 2nd column if their values are equal to the cell in my origin / destination matrix I want the number times this occurs. The formula would be something like: =count(if(A2:A20=E5) & if(B2:B20=F4)).
I have attached a spreadsheet that outlines what I require. Any help would be grately appreciated.

Jonny.

First time posting...been working on this for a few days now, hoping someone can help with the below situation; need solution quickly for meeting on Wednesday...

I have three tables (TenLim, TwentyLim & Excess - each 250+ lines over 6+ columns wide - this can't be changed) that are factoring into one formula dependent on a single number answer between 10-20.

The formula is:
=IF(D7="10",(VLOOKUP(D11,TenLim,2,FALSE)),IF(LOOKUP(D7,{"11","12","13","14","15","16","17","18","19" },(VLOOKUP(D11,Excess,2,FALSE))),IF(D7="20",(VLOOKUP(D11,TwentyLim,2,FALSE)))))

I receive a 'FALSE' answer on the above formula. I believe it's from the first VLOOKUP situation, due to D7=11 (If D7=10, the formula works). I thought my first IF statement's false_value was the second IF statement, but it doesn't work. I placed the LOOKUP function in the second IF statement due to having more than seven responses; is this the problem?

Any info you can pass along would be greatly appreciated! THANKS!

Hi,

I'm trying to put together a formula that will perform a vlookup, and if statement, and concatenate. I can do each of those functions but can't get them all to work together. I have attached a sample sheet of data so you can see what I'm trying to do.

If you are looking at the sheet I want the formula to concatenate the data from DateA and DateB if Tracking A and Tracking B are the same. Example: If Tracking A and Tracking B are both 123460 I would likethe formula to concate DateA which is abc and DateB which is def giving me a result of abcdef.

Any help would be most welcome. I thank you all in advance for your time.

Eddie.

I'm trying to use a Case and If statement. I have the formulas and each one works independently but I can't get them to work together. It's probably something as simple as a parenthesis or a comma somewhere but I can't figure is out.

this is what I have:
Case ([Status], "Paid in Full", "99FFFF", "Owes Retainage", "Pink", "Partial Paid", "Pink", "VOID", "99FFFF", "Credit Issued", "99FFFF", "")

If (Today() - [Date[ > Days (30), "FF3333", "")

First of all, can I run lookups and IF statement formulae against pivot tables?

Here's what I'm trying to accomplish in a nutshell: I have 2 tabs, 1 is a pivot table and the 2nd tab will be used to summarize what's in tab 1 by using formula. I attached the file for demonstration

TAB 1 is a pivot table. I can copy/paste it as values in case formulae such as IF and lookups don't work with pivot tables

TAB 2 is where I am trying to summarize the numbers from TAB 1.

I'm trying to insert a formula in where the x is in TAB 2. Now I know I can concatenate columns A and B in TAB 1 and then run a vlookup in TAB 2 to get the results but I would like to know how I can use a formula that lets me say that "look for what's in A2 in TAB 2 and find it in TAB 1, then look what's in B2 in TAB 2 and find it in TAB 1. If found give me the number from TAB 1, or else give me 0).

I hope it made sense. Thank you very much

Again, thanks in advance to all those taking the time out to read this thread and support me in resolving my problem. Muchos Appreciados.

Please refer to the attachment in the first instance.

What I need is a macro to loop down the list of names in sheet 1 and when it reaches the last line for a specific name - insert a VLOOKUP and IF formula to retrieve data from sheet 2 and test the value against info in another cell.

If I use the first agent Ibrahim Abdiselam as an example. I want the macro to search down the list until it gets to the last entry for Ibrahim, which in this case is row 6, then in H6 enter a VLOOKUP formula which looks up the value in A6 (the agents name) in Sheet 2 columns A:B and returns the value in column B. Once finished, I need the macro to continue to the next name and repeat, all the way to the bottom of the list.

Once thats done - I then need, possibly an IF statement to be entered in the adjacent cell to the cell that has the VLOOKUP formula. So for example in Ibrahims case in I6, and calculate whether the time D6 is within 3mins either side of the time returned in H6 amd if so return a positive result. So for example, in Ibrahims case, as 1609 is within 3 mins of 1610 then it would return a positive result. Alternatively, if the time in D6 was 1612, then it would still return a positive result, because it's within the 3min buffer. Again I need this to repeat for the last entry of every agent all the way to the end.

Hope you can help.

Tidy Butt

I have 3 columns with different text data. A1:A20 has text data (A, B, C, D), B1:B20 has either a "YES" or "NO", and C1:C20 has either a "YES" or a "NO" in each cell.

I need a formula that looks at Column A and counts all the cells containing "B" that also have a "YES" in Column B and a "YES" in Column C (in the same row that "B" is in).

I have a feeling that this will require a COUNTIF, AND, and IF statements, but I am having a hard time figuring this out. Any suggetsions?

Hi all.

I am looking for help with the following. I am semi new to the formula's and funtions/referencing and my college prof. well, kind of didn't explain very well and we're not allowed to email her.HELP

I basically just need to know what to put in each box of the VLOOKUP and "IF" function.

i.e for the "IF" function

If the the value in column F6 is more than $500, add 25%. If it is less than $500 and 10%.

i.e for the VLOOKUP
I want to determine the cost of the box in I7 by looking up the cubic feet value in H7. Value cost is in F7. I also MUST use absolute reference but I don't know how to do that.

Thank you for saving a new college girl out!

Is there a way to use Sumproduct and and If statement together?

I am trying to setup a formula to Sum columns from another spreadsheet if a condition is met and then sort those choices by location. The product key is on a different file than the first set of data.

File 1
[Product] [2005] [2006] [2007] [2008] [2009] [2010] [State]
[1250] [15.20] [20.90] [12.80] [30.00] [15.00] [10.00 ] [NJ]
[1250] [54.00] [40.00] [60.00] [55.00] [45.00] [30.00] [NY]
[320] [2.00] [3.00] [4.00] [4.00] [3.00] [2.00] [NY]
[320] [1.50] [2.50] [3.50] [3.50] [2.50] [1.50] [NJ]

File 2
[Product]
[1250] [Product A]
[320] [Product B]

So I am trying to get a list the adds the data by product by state and sum each year using sumproduct and an if statement. Am I going about this all wrong? I am using sumproduct because using sumif I need to have the sources all open or get reference errors. I am dealing with hundreds of lines of data and trying to automate the process of breaking out products by location and sum the years prices.

Any help is greatly appreciated. Thanks

Hello,

Im trying to combine Vlookup and Match formulas to extrapolate data from excel lisiting but it does not work for me.

Please see attached file to this post.

In my case im trying to pick up "Postpaid HS" values (on Postpaid Hs tab) from Value tab, sorted by GL code (e.g.
A457) and match it for each date of the month (e.g. from 01/12/11 to 31/12/11 etc). As you can see from my example my formula picks up all values for the 1st of Dec but does not for future dates, i.e. 02/12/11.

Could anybody please help me?

Many thanks in advance

I have a workbook with 4 spreadsheets. Sheet 1 maintains all numbers and compares 2 cells reflecting "Match ABC" and "DIFF". Sheet 2 contains all numbers from sheet 1 with a "DIFF" value. Again, it will compare 2 cells and reflect "MATCH DEF" and "DIFF". Sheet 3 contains all numbers from sheet 2 with a "DIFF" value; comparing 2 cells to reflect "MATCH GHI". Sheet 4, the final sheet, contains the remaining numbers and compares 2 cells again to reflect "MATCH JKL" and "DIFF".

Sheet 1 is the master sheet. I want to have it reflect the "match" result from all 4 sheets for the numbers...such as:

#
123 Match ABC
456 Match ABC
789 Match GHI
1234 Match JKL
12345 DIFF (this could happen)
123456 Match DEF

I've tried using the INDIRECT function combining with VLookup and IF to no avail.

Can this be done?

i want to combine this two if statements and i'm pulling out my hair on how to do it
=IF(ISNUMBER(SEARCH("*refurbished*",H2)),"refurbished","new")

IF(ISNUMBER(SEARCH("*open box*",H2)),"open box","new")

basically i want to check if the product is refurbished if it is then i want to put refurbished if open box i want to put open box otherwise i want to put new i tried multiple things but noting seems to work please help

Hello. I am trying to create a formula that combines the formulae Max and VLookup.

Here is my scenario: I am extracting data from a spreadsheet called "Sales Report" (see below).

Account # Sales Rep YTD Sales
123456 J. Young $5,879.59
123456 W. Stuart $2,356.74
123456 C. Fraser $157.50
123456 S. West $1,658.96
789456 N. Ferris $562.50
789456 J. Young $4,896.35
789456 C. Fraser $1,257.35
852369 N. Drew $456.50
852369 A. Brody $785.52

I am doing a summary of the top sales performers by account #. For example, account 123456, range = A2:C5, look for the highest YTD Sales and retrieve the Sales Rep Name. The ideal output will be:

Account # Sales Rep YTD Sales
123456 J. Young $5,879.59
789456 J. Young $4,896.35
852369 A. Brody $785.52

And if possible, because the the actual report is large - automatically have excel pick the range in every change in Account #.

Please help. I'm not even sure if this is do-able.

Your feedback and suggestion is greatly appreciated.

Best Regards,
Liza