Using Vlookup and IF/And Statements in Excel

Microsoft Excel 2003 (full product)
June 23, 2010 at 14:03:18
Specs: Windows XP
Need some help with VLOOKUP and using more then one IF statement.
I searched around but am still confused.

Basically this is what I have:

Or I need a way to use both these formulas:


Any help is greatly appreciated as I'm trying to use more then one variable I'm unsure of how to do it.


See More: Using Vlookup and IF/And Statements in Excel

Report •

June 23, 2010 at 15:09:33

From what I can gather from your formulas, you want to return one of four columns in the range A2:D4 on the freight worksheet, based on whether C9 is Blank or not and whether G30 is less than 23000 or not.
The actual value returned from columns 2 to 4 in the range depends on the value in B5 (using the VLOOKUP() function).

If I have this right, there are four initial conditions:
C9 blank/G30<23000
C9 not blank/G30<23000
C9 blank/G30>=23000
C9 not blank/G30>=23000

C9 blank/G30<23000 returns column 2 data as far as I can see, but it is not clear which column to return for the other 3 combinations.

Can you specify the columns for all four combinations.


Report •

June 23, 2010 at 15:47:11
You are correct.
I am trying to use a formula where on my worksheet:
column 1 is for if C9 is blank and G30<23000
column 2 is for if C9 not blank and G30<23000
column 3 is for if C9 is blank and G30>=23000
column 4 is for if C9 not blank and G30>=23000

I'm using vlookup as its freight rates for different carriers, based on single or duel temp(C9) and the weight(G30).

The output should be a dollar amount based on what is in the worksheet.

Hope that helps explain more.

Report •

June 23, 2010 at 15:50:43
Forgot to add that column A in the worksheet is numbers which correspond to the different Carriers, 1,2,3...etc

Report •

Related Solutions

June 23, 2010 at 16:41:45

Here is the formula I used:

You will need to change it to refer to the freight worksheet in a different workbook. For this example I just used one workbook with freight as a second worksheet.

The formula basically uses the IF() statements to return a column number (2, 3, 4 or 5)
This value is then used as the column or offset value for VLOOKUP()

Hence you only need one VLOOKUP()

The First IF() statement tests C9 to see if C9="" or not
In either case - C9 is blank or C9 is not blank - there is a further IF() statement - both the same, testing if G30 <23000 or not.

Hope this gives you enough to solve your problem.

If not please post back with information on the formula you are actually using and what results or errors occur.
I could only test this on dummy information.
I used 1, 2 and 3 in A2, A3 and A4 of the table on the freight page, and B5 used data validation to limit values to 1, 2 or 3.


Report •

June 23, 2010 at 16:59:02
Works perfect.

Seems so simple now looking at it. This will easily help save me 5 mins a day on average.

Thanks so much :)

Report •

June 23, 2010 at 17:14:32
You're welcome,

and thanks for the feedback.



Report •

Ask Question