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","Name 5","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!

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

"MsBeverlee" wrote in message
...
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","Name 5","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!

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
On Feb 21, 6:23 pm, MsBeverlee
wrote:
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","Name 5","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!

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","Col eman","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!

"Pete_UK" wrote:

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
On Feb 21, 6:23 pm, MsBeverlee
wrote:
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","Name 5","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!

Any suggestions?

The lookup_vector MUST be sorted in ascending order.

Try this:

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

Biff

"MsBeverlee" wrote in message
...
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","Col eman","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!

"Pete_UK" wrote:

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
On Feb 21, 6:23 pm, MsBeverlee
wrote:
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","Name 5","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!

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

On Feb 21, 7:13 pm, MsBeverlee
wrote:
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","Col eman","Thomas","Allen","D*avis"},{"#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!



"Pete_UK" wrote:
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
On Feb 21, 6:23 pm, MsBeverlee
wrote:
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","Name 5","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!- Hide quoted text -

- Show quoted text -

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

Argh! I forgot to do that!

Biff

"Pete_UK" wrote in message
ups.com...
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

On Feb 21, 7:13 pm, MsBeverlee
wrote:
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","Col eman","Thomas","Allen","D*avis"},{"#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!



"Pete_UK" wrote:
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
On Feb 21, 6:23 pm, MsBeverlee
wrote:
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","Name 5","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!- Hide quoted text -

- Show quoted text -

Another one:

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

Biff

"T. Valko" wrote in message
...
I've also had to change the order of the second array.

Argh! I forgot to do that!

Biff

"Pete_UK" wrote in message
ups.com...
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

On Feb 21, 7:13 pm, MsBeverlee
wrote:
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","Col eman","Thomas","Allen","D*avis"},{"#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!



"Pete_UK" wrote:
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
On Feb 21, 6:23 pm, MsBeverlee
wrote:
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","Name 5","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!- Hide quoted text -

- Show quoted text -