INDEX & MATCH formula problem

[securityman]

Hello All,

I have a table (DRIVERS) in cells z1:ai50. In Col Z are the names of
the company drivers (z2:z50-named range of "partner"). In Col AA are
their Employee #'s. (aa2:aa50) In the other columns are various
information about each driver. The cells in row 1 (z1:ai1 - named
range of "cat")

In cell B9 I want to type a driver's name and have the results (his
Employee #) show in cell B14.

I have this formula in B14. It gives me #NA in cell B14. When I
evaluate the formula it tells me that there is no value for the first
MATCH in the formula. The 2nd MATCH works fine (it gives me column 2 -
which is where the Employee #'s are in the table). I have read a lot
about MATCH, INDEX and everyone seems to change things just a little
bit. Some add +1 to the end of the MATCH part. I need an exact MATCH
so I place a 0 as the last argument.

If I use VLOOKUP it gives me an Employee #, but it give the one above
the person for whom I was searching.

Can someone show me where my INDEX/MATCH formula is wrong or should I
be using VLOOKUP?


Formula:

=INDEX(drivers,MATCH(B9,partner,0),Match("employee",cat,0))


Thanks

 

[Domenic]

=VLOOKUP(B9,Z2:AA50,2,0)

Hope this helps

 

[securityman]

Domenic,

Thanks for the reply.

Tried your suggestion, but it returned the Employee # of the driver on
row above the one I requested.

This is what happens everytime I use VLOOPUP.

Any other suggestions?:confused

 

[Domenic]

Make sure that the lookup value exactly matches the value you're tryin
to match.

Specifically, make sure there are no spaces before or after names, o
more spaces in between names, etc...

Try,

=VLOOKUP(TRIM(B9),Z2:AA50,2,0

 

[securityman]

Still not working....

My list of name do have space in them (between the last name an
firstname)........ex: Doe, John

I put it exactly like that in B9. I've also tried all caps and al
lower case letters when I enter the name in B9

 

[Domenic]

Try...

=VLOOKUP(TRIM(B9),TRIM(Z2:AA50),2,0)

...entered using CTRL+SHIFT+ENTER