|
VLOOKUP results on last names
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 |
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) "James" wrote in message ... 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 |
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 "James" wrote: 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 |
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 "Bob Phillips" wrote in message ... 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) "James" wrote in message ... 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 |
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) "James" wrote in message ... 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 "Bob Phillips" wrote in message ... 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) "James" wrote in message ... 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 |
Bob,
I figured this out and it worked wonderfully!! thanks for the time and clarification!! James "Bob Phillips" wrote in message ... 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) "James" wrote in message ... 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 "Bob Phillips" wrote in message ... 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) "James" wrote in message ... 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 |
| All times are GMT +1. The time now is 05:50 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com