Combining IF with VLOOKUP

[Ricki Miles]

I am using Excel 2003. I would like to be able to lookup a value in a list.
What the lookup returns will be determined by whether or not there is an "X"
in one of the columns. For example, it will look up item 123 and if item
123 (in column A) has an "X" in the column beside it, the data returned
will be from columns C and D. If the item does not have an "X" in the
column beside it, the data returned will be from columns E and F. I am not
sure how to string together VLOOKUP with IF.

Thank you,

Ricki

 

[Shane Devenshire]

Hi,

First point is that in general you only return data from one column at a
time with VLOOKUP, not from column C and D at once.

The basic formula would be

=IF(VLOOKUP(A1,Sheet2!A1:F100,2,FALSE)="X",VLOOKUP(A1,Sheet2!A1:F100,3,FALSE),VLOOKUP(A1,Sheet2!A1:F100,5,FALSE))

or a shorter variation:

=VLOOKUP(A1,Sheet2!A1:F100,IF(VLOOKUP(A1,Sheet2!A1:F100,2,FALSE)="X",3,5),FALSE)

 

[Ricki Miles]

Thank you, Shane. I will give it a try,

Ricki

Hi,

First point is that in general you only return data from one column at a
time with VLOOKUP, not from column C and D at once.

The basic formula would be

=IF(VLOOKUP(A1,Sheet2!A1:F100,2,FALSE)="X",VLOOKUP(A1,Sheet2!A1:F100,3,FALSE),VLOOKUP(A1,Sheet2!A1:F100,5,FALSE))

or a shorter variation:

=VLOOKUP(A1,Sheet2!A1:F100,IF(VLOOKUP(A1,Sheet2!A1:F100,2,FALSE)="X",3,5),FALSE)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire