Nested IF with VLookup

K

karen

Is it possible to have a vlookup function within an IF
statement? If so, what is the correct syntax. Everytime I
try to put it in, I just get the written formula that I
typed in. It goes something like this:
=If (B3=27,"vlookup=('Product Table'!C3,Products,D) What
am I doing wrong?
thanks
 
N

Norman Harker

Hi Karen!

It's difficult without more data but the syntax might be gathered from
the following:

Try:

=If (B3=27,VLOOKUP(B3,'Product Table'!C3:D15,2),"")

In this case if B3 is 27 we look up B3 in the second column of the
table on the Product Table Sheet in the range C3:D15. If B3 is not
equal to 27 it returns an empty string. Better than using the address
of the table would be to name it (e.g. MyTable01) and then it becomes
a little easier.

Best approach is to get your VLOOKUP working without worrying about
the condition first. Then "nest" it in the IF function.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Monday 21st July: Belgium (National Day),
Bolivia (Martyrs' Day), Guadeloupe (Victor Schoelcher Day), Guam
(Liberation Day), Japan (Marine Day), Malta (St. George Festa), Peru
(Feria Local Fronteriza Peruano Ecuatoriana), St. Martin (Schoelcher
Day).
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
J

John Wilson

karen,

Looks like you're missing a few arguments in your formula:
(and you have a few errant characters)

The basic IF structure is:
=IF(condition,doiftrue,doiffalse)

For your formula:
=If (B3=27,vlookup('Product Table'!C3,Products,D),"AndIfNotTrue")

Still a few errors....
The "D" above is an index into your and it needs to be a number
or a cell reference with a number.
'Product Table'!C3,Products
should probably be just:
'Product Table'!Products
if "Products" is the range that you're doing the vlookup into.

Also, you didn't specify the True/False argument for your vlookup
(meaning that it's assumed that your table is in numeric/alphabetic
order
and you're looking for the "closest" match without going over.)
Is this the case??

John
 
J

John Wilson

karen,

error in my post....
'Product Table'!C3,Products
should probably be just:
'Product Table'!Products
if "Products" is the range that you're doing the vlookup into.
Click to expand...

disregard the above

John
 
G

Guest

I got it, I got it!!. I actually used a splice of
everyone's comments, because I also needed a formula that
would not display the #n/a. Thanks everyone!!!!!!
 
Top