Inex and Lookup searches

[Cynthia]

Using MS Office Professional 2003
Given:
Four columns (A-D) titled ID, Name, Job, Job Description
Data range is A2158
Data is sorted alphabetically by last name:

ID Name Job Job Description
502733 Baker,Mark DIR VP MFG
502160 Jones,Debra DIR VP Finance
501565 Michaels,Susan DIR VP MKTG
504729 Smith,David DIR VP Technology

I have four names on the same sheet that already appear on the list. They
are listed from A:164:A167 in random alphabetical order by last name.

I want to search the data range referenced above and populate the ID number
in column B and Job Description in column C for my individual’s names.

Formula for ID: =INDEX(A2:A158,MATCH(A164,$B$2:$B$158,TRUE),1)
Formula for Job Description: =LOOKUP(A164,$B$2:$D$158)

Problem: The formulas work for cells A164 and A165. Cells A166 and A167
return the ID number and Job Description listed for the individual appearing
one row before my individual in the data range. In other words, my
individual’s information is listed in row 38 and the formula is returning the
information for row 37.

I don’t understand why this is happening or how to resolve it. Help is much
appreciated!

Cynthia

 

[John C]

With the data not in alphabetical order, and since you are not using exact
matches, excel will search down until it finds an approximate value and then
go by that data.

To fix, I believe all you need to do is change the TRUE in your match
statement for ID to a 0 (which will then look for an exact match. 1, 0, and
-1 are the choices here).
In addition, instead of a LOOKUP for your Job Descriptino, you would want a
VLOOKUP:
=VLOOKUP(A164,$B$2:$D$158,3,FALSE)