VLOOKUP results on last names

J

James

Hi Everyone!

I'm using VLOOKUP to match last names. The problem i'm having is multiple
people with the same name. VLOOKUP returns the fist match, what are some
suggestions and ideas so that i can get a list to choose from?
Also, is there a way to do wildcard searches in excel in a VLOOKUP or does
it absolutely have to be an exact match?

any ideas?

TIA

James
 
B

Bob Phillips

You could lookup on a combination of last and first names, like

=INDEX(O2:O100,MATCH(A1&B1,M2:M100&N2:N100,0))

which is an array formula so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
B

B. R.Ramachandran

Hi,

One possibility: You could use Conditional Formatting to shade all the rows
containing the last name you are looking for.

Regards,
B. R. Ramachandran
 
J

James

Hi Bob,
thanks for replying!

could you explain this a little more? I've never used the INDEX function.
i will try to learn more about the INDEX and MATCH formulas while i wait for
your response. Thanks again!!

JAMES
 
B

Bob Phillips

Well what it does is first find the row index of the concatenated last and
first names in the concatenated lists in columns M & N. It returns that
index to the INDEX function which uses it to retrieve the corresponding
entry in column O.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
J

James

Bob,
I figured this out and it worked wonderfully!!

thanks for the time and clarification!!

James
 
Top