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" wrote:

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" wrote:

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","name 2",...,"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" wrote:

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 formula
=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?

"Don Guillett" wrote:

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"})),"",LOO KUP(D5,{"Name1","Name2","Name3","Name4","Name5","N ame6","Name8","Name9"},{"#1
VIP","#2 VIP","VIP","VIP","VIP","VIP","VIP","VIP"}))
--
Don Guillett
SalesAid Software

"MsBeverlee" wrote in message
...
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.