V or XLOOKUP a 4 digit number from the last 4 characters of a range of 12 digit numbers

Question

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

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

user_9758194 · Answer

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.

user_4279155 · Answer

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)

user_8162520 · Answer

EDIT

• Formula used in cell E2

=TEXTJOIN(" or ",,FILTER($A$2:$A$6,
D2=--REPLACE($B$2:$B$6,1,LEN($B$2:$B$6)-4,""),
"Typed wrong or Does Not Exist"))