Combining IF with VLOOKUP

  • Thread starter Thread starter Ricki Miles
  • Start date Start date
R

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
 
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)
 
Thank you, Shane. I will give it a try,

Ricki

Shane Devenshire said:
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
Click to expand...
 
Back
Top