LOOKUP function yielding a #N/A result

[MsBeverlee]

Due the size restriction of the IF function, I have used the LOOKUP function
for my conditional formula. It is yielding the results I want, but when the
criteria does not match, the result is the error #N/A. Here is the formula I
am using:

=LOOKUP(B4,{"Name1","Name2","Name3","Name4","Name5","Name6","Name8","Name9"},{"#1 VIP","#2 VIP","VIP","VIP","VIP","VIP","VIP","VIP"})

Is there anyway to use the IF function so that if the result of the above
formula = #N/A, the cell is blank. Or can I set a conditional format so that
if it's #N/A, the cell is highlighted or font color different?

Also, with this same formula, is it possible to have the LOOKUP reference
more than 1 cell (i.e. =LOOKUP(B4 &" "& B5, . . . .)?

Thanks for your help.

 

[Don Guillett]

try
=IF(ISNA(yourformula),"",yourformula)

=IF(ISNA(LOOKUP(D4,{"Name1","Name2","Name3","Name4","Name5","Name6","Name8","Name9"},{"#1
VIP","#2
VIP","VIP","VIP","VIP","VIP","VIP","VIP"})),"",LOOKUP(D5,{"Name1","Name2","Name3","Name4","Name5","Name6","Name8","Name9"},{"#1
VIP","#2 VIP","VIP","VIP","VIP","VIP","VIP","VIP"}))

 

[MsBeverlee]

Thanks, Don.

I am still having a problem with the overall formula. When I copy the
formula down the column, some of the cells that do not meet the criteria are
still yielding the result of "VIP". For example: cell B4 does not equal
"Name1", but it is still yielding the result "#1 VIP".

In my formul
=LOOKUP(B4,{"Name1","Name2","Name3","Name4","Name5","Name6","Name8","Name9"},{"#1 VIP","#2 VIP","VIP","VIP","VIP","VIP","VIP","VIP"})

the cell reference B4 is actually a link to another worksheet (i.e. B4 in
worksheet2 is actually the formula = 'worksheet1'!F2). Will this impact my
LOOKUP function result?

 

[bj]

your problem is that lookup does not look for exact matches
I would recommend something like
=if(isna(vlookup(b4,{"name1","name2",...,"name9";"#1 VIP","#2
VIP",...,"VIP"},2,0)),"",vlookup(b4,{"name1","name2",...,"name9";"#1 VIP","#2
VIP",...,"VIP"},2,0))
This will give you exact matches only.
if the list is volitile, I would recomend using a range rather than keeping
everythingin the formula

 

[MsBeverlee]

Thanks, BJ -

However, it still isn't working. Basically what I'm trying to do is create
a formula in cell E4 that IF cell B4="Name1" the result will be "#1 VIP", IF
B4="Name2", result is "#2 VIP", and so forth.

I tried using the VLOOKUP function as you suggested, but it appears column
reference "2" is generating the second name in my array (i.e. "Name2").

Thanks!

 

[bj]

change Vlookup to Hlookup

I am not sure what I am doing wrong, Cause I normally use Vlookup in this
situation, and it works, Today vlookup didn' work but Hlookup does.

 

[MsBeverlee]

Hi, BJ! It worked with the HLOOKUP! Thanks so much!

Now . . . another question. Is it possible to use this same formula but
have it lookup 2 separate columns? For example, I need it to look up both
the First Name (which is in cell $A2) and the Last Name(which is in cell
$B2), and then if both of these match, return the results I want (i.e. "VIP"):

Can this be done?

Thanks!