1. ## Reverse Lookup (2000)

I am trying to figure out how to do a reverse lookup. I am using the following data for example. I am using columns C, D and E
Client Managers CM. #
Jones, Judy 14
Dominici, Mark 02
Henry, David 83
Sproule, Bob 71
Stine, Shirley 34

I am trying to put the CM# and get the Client Manager Name.
The formula I am using in M7 is =INDEX(C3:E10,MATCH(M6,E3:E10,0),3)

When I put 71 in M6, the result I get is 71. I would like to get the name Sproule, Bob

Does anyone know how to do this?

Thanks

2. ## Re: Reverse Lookup (2000)

See <post#=244408>post 244408</post#> by sdckapr about "VLookup to the left".

3. ## Re: Reverse Lookup (2000)

The column index argument of INDEX in your formula asks INDEX to pull off the value in E. That's why you get back what you're looking up.

Since there could be duplicate names in C and D, like 2 Jones in C with diffrent first names in D, you'd better off if you create an additional column next to the CM.#.

That is:

In E enter & copy down:

=C3&CHAR(127)&D3

Now you can use either a VLOOKUP formula...

=SUBSTITUTE(VLOOKUP(M6,\$E\$3:\$F\$10,2,0),CHAR(127),C HAR(32))

or an INDEX/MATCH formula:

=SUBSTITUTE(INDEX\$F\$3:\$F\$10,MATCH(M6,\$E\$3:\$E\$10,0) ),CHAR(127),CHAR(32))

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•