Sheet 1 column A has Names and Column B has 12 Digit ID Numbers.
Sheet 2 has a single column full of the last 4 numbers of random employees ID's.
I need to X or Vlookup Sheet 2 to match those last 4 numbers with full 12 digit ID numbers. If there are many matches, I would like to list them out.
Here is what I have but it Produces a #N/A error:
=XLOOKUP(Sheet2!$A$1,Sheet1!$B$1:$B$900,Sheet1!$A$1:$A$100)
I can't find a way to only search the last four digits of each of the range cells in Sheet1 (the range of 12 digit ID's). Please help.
Sample Data:
Sheet 1
| Name | 12 Digit ID |
|---|---|
| John | sample111TypoHere1344 |
| Jake | 123456782567 |
| Eliza | 558456782567 |
| Tony | 558456789911 |
| Jane | 564231846155 |
Sheet 2
| Last Digit ID's | Guess for Employee's Full ID From Searching Sheet 1 |
|---|---|
| 2567 | |
| 9911 | |
| 4555 | |
| 1234 |
Expected output:
Sheet 2
| Last Digit ID's | Guess for Employee's Full ID From Searching Sheet 1 |
|---|---|
| 2567 | Jake or Eliza |
| 9911 | Tony |
| 4555 | Typed wrong or Does Not Exist |
| 1234 | Typed wrong or Does Not Exist |
CodePudding user response：
Right, a lot of hints towards FILTER(), and looking at your data I do think the following should work:
Formula in E2:
=TEXTJOIN(", ",,FILTER(A$2:A$6,--RIGHT(B$2:B$6,4)=D2,"Nothing"))
The double unary is there since I kind of expect the 12-digit ID's to be text.
CodePudding user response：
You might use a VLookup(), based on a helper column, based on the simple formula =MOD(A2,10000) as a way to get the last four digits of a number.
Here an explanation in a screenshot:
The VLookup() formula is the following:
=VLOOKUP(D2,$B$2:$C$3,2)
CodePudding user response：
EDIT
You may try this as well, after 