Microsoft Excel 2003 (full product)

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:

=IF(AND(ISBLANK(C9),G30<23000,VLOOKUP(B5,[Freight.xls]Freight!A2:C4,2,FALSE),IF(ISBLANK(C9),G30>23000,VLOOKUP(B5,[Freight.xls]Freight!A2:C4,3,FALSE)),VLOOKUP(B5,[Freight.xls]Freight!A2:E4,4,FALSE),VLOOKUP(B5,[Freight.xls]Freight!A2:E4,5,FALSE)),VLOOKUP(B5,[Freight.xls]Freight!A2:E4,5,FALSE))Or I need a way to use both these formulas:

=IF(ISBLANK(C9),VLOOKUP(B5,[Freight.xls]Freight!A2:C4,2,FALSE),VLOOKUP(B5,[Freight.xls]Freight!A2:C4,3,FALSE))

=IF((G30<23000),VLOOKUP(B5,[Freight.xls]Freight!A2:C4,2,FALSE),VLOOKUP(B5,[Freight.xls]Freight!A2:D4,4,FALSE))Any help is greatly appreciated as I'm trying to use more then one variable I'm unsure of how to do it.

Thanks,

Hi, 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>=23000C9 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.

Regards

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>=23000I'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.

Forgot to add that column A in the worksheet is numbers which correspond to the different Carriers, 1,2,3...etc

Hi, Here is the formula I used:

=VLOOKUP(B5,freight!A2:F4,IF(C9="",IF(G30<23000,2,4),IF(G30<23000,3,5)),FALSE)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.Regards

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

Thanks so much :)

You're welcome, and thanks for the feedback.

Regards

Humar

Ask Your Question

Weekly Poll

Do you think Slack's direct listing "IPO" is a good idea?

Discuss in The Lounge

Poll History