Log In
Register
Home
July 15, 2020
Offline
Sheet1, column A has the following info:
| Rep Name |
| John Jones |
| Paul Smith |
| Kevin Korn |
| Brian Beede |
| Pam Person |
| Lois Lane |
| Brian Dudge |
| Pam Person |
| Annis Anaya |
| Chad Chu |
A tab named List has the following info:
| Advisor # | Advisor Name |
| 10 | Kevin K Korn |
| 59 | Pam L Person |
| 94 | Brian C Dudge |
| 140 | Chad M Chu |
| 286 | John J Jones |
| 307 | Lois L Lane |
| 320 | Paul O Smith |
| 384 | Annis A Anaya |
| 556 | Brian X Beede |
I want to match the names on Sheet1 with the names on List tab, and populate a Column on Sheet1 with the Advisor# on List tab. Output should look like this:
| John Jones | 286 |
| Paul Smith | 320 |
| Kevin Korn | 10 |
| Brian Beede | 556 |
| Pam Person | 59 |
| Lois Lane | 307 |
| Brian Dudge | 94 |
| Pam Person | 59 |
| Annis Anaya | 384 |
| Chad Chu | 140 |
The formula I'm using is =INDEX(List!A:B,MATCH(A2,List!B:B,1),1). But the result I get is like this:
| John J Jones | 286 |
| Brian X Beede | 556 |
| John J Jones | 286 |
| Advisor Name | Advisor |
| Lois L Lane | 307 |
| Lois L Lane | 307 |
| Brian C Dudge | 94 |
| Lois L Lane | 307 |
| Advisor Name | Advisor |
| Brian C Dudge | 94 |
As you can see, the names and numbers are not matching up. Am I doing something wrong?
Trusted Members
December 20, 2019
Offline
We really do need work book examples rather than pasted in data as we just waste time recreating the data you already have.
The issues you have is that you are not matching like for like and the format of the formula is slightly wrong
Brian Beede is not the same as Brian X Beede, so you are doing an appoximate match indicated by the 1 at the end of the match part of the formula, you need to clean up the lookup table to match the data.
You could do with flash fill which is the easist, althought not perfect with the names you have on here (quite a lot of alliteration!) or you could also do a formula like =LEFT(G2,SEARCH(" ",G2))&RIGHT(G2,LEN(G2)-SEARCH(" ",G2,SEARCH(" ",G2)+1)) if the names are all in the same format (first initial last)
Then the formula you only need the index to return what want to return which means you can also drop the column arguement
=INDEX(List!A:B,MATCH(A2,List!B:B,1),1)
Would be
=INDEX(List!A:A,MATCH(A2,List!B:B,1))
but then you also need an exsact match with the clean lookup table so it ends up as
=INDEX(List!A:A,MATCH(A2,List!B:B,0))
July 15, 2020
Offline
Thank you. I will apply your solution and advise. It is true that my data table is entirely first initial last, with a few suffixes (Jr., III, etc.) and a few hyphenated last names, but I can clean those up manually if needed. And the data has a lot less alliteration than my sample! I apologize for forcing you to recreate my sample, and will try to be more sensitive to that in the future.
1 Guest(s)
