Help w/ LOOKUP function!!

[MsBeverlee]

I having a problem with the LOOKUP function. I follow the example on the
Excel Help, but it doesn't seem to be generating the correct result. Here is
the formula I'm using:

=LOOKUP(A2,{"Name1","Name2r","Name3","Name4","Name5","Name6","Name7","Name8"},{"1","2 ","3","4","5","6","7","8"}).

Cell A2 does equal "Name1", but the formual is yielding the result "4",
instead of "1". According the the Excel Help, the formula should

"Looks up the value in A2 ("Name1") in the first row of the array, finds the
largest value that is less than or equal to it, and then returns the value in
the last row of the array that's in the same column."

This doesn't seem to be happening and my formula keeps returning a value
that is NOT is the same column.

Any suggestions? Am I inputting the formula incorrectly?

Thanks for your help!

 

[T. Valko]

The lookup_vector MUST be sorted in ascending order.

Is Name* the REAL value or is that just a made-up example?

Also note that the values in your result_vector are TEXT values, not numeric
values. Enclosing numbers in " " makes them TEXT.

If those are the real names then:

=RIGHT(A2,1)

will do the same thing. Is Name2r a typo?

Biff

 

[Pete_UK]

It works for me - I copied your formula directly from your posting
into Excel and tried out a few names in A2 to test it out.

If I enter Name2 in A2 then I get the result 1, as it is finding the
nearest value that is less than A2 (and your formula has Name2r). If I
enter Name 1 (i.e. with a space) then the formula returns #N/A.

I presume your Name1, Name2 etc are just examples - if you are using
real names then ensure that they are in order in your list.

Hope this helps.

Pete

 

[MsBeverlee]

Yes, those names are just examples. But it still isn't working. For
instance, I just added the names and re-inserted the formula and it still is
giving me the wrong results:

=LOOKUP(A2,{"Smith","Jones","Taylor","Wright","Coleman","Thomas","Allen","Davis"},{"#1 VIP","#2 VIP ","VIP","VIP","VIP","VIP","VIP","VIP"})

If I type in Smith in A2, the result I'm getting is #2 VIP. The result
should be #1 VIP. It's as if it's not matching the data. I need it to match
so that if I input "Jones" into cell A2, I need it to return the value "#2
VIP". Any suggestions?

Thanks!

 

[T. Valko]

Any suggestions?

Try this:

=LOOKUP(A2,{"Allen","Coleman","Davis",0},{"#1 VIP","#2 VIP ","VIP",""})

Biff

 

[Pete_UK]

You need to rearrange your names, like so:

=LOOKUP(A2,
{"Allen","Coleman","Davis","Jones","Smith","Taylor","Thomas","Wright"},
{"VIP","VIP ","VIP","#2 VIP","#1 VIP","VIP","VIP","VIP"})

Obviously, I've also had to change the order of the second array.

Hope this helps.

Pete

 

[T. Valko]

I've also had to change the order of the second array.

Argh! I forgot to do that!

Biff

You need to rearrange your names, like so:

=LOOKUP(A2,
{"Allen","Coleman","Davis","Jones","Smith","Taylor","Thomas","Wright"},
{"VIP","VIP ","VIP","#2 VIP","#1 VIP","VIP","VIP","VIP"})

Obviously, I've also had to change the order of the second array.

Hope this helps.

Pete

 

[T. Valko]

Another one:

=IF(A2="","",IF(A2="Smith","#1 ",IF(A2="Jones","#2 ",""))&"VIP")

Biff