Nested IF with VLookup

[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

 

[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'!C315,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 C315. 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).
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[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

 

[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.

disregard the above

John

 

[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!!!!!!