Find all matches that use the 4 digits in sheet1 column A to find the phone numbers by matching the last 4 numbers in sheet2 column b and then put the full phone number(s) in column B in sheet1. The code I use works but it doesn't show all the matches. I want to match all so some of the 4 digits may have multiple matches. The code I have in sheet1 Column B1 is
=INDEX(Sheet2!B:B, MATCH(RIGHT(A3,4), RIGHT(Sheet2!A:A,4), 0))
Sheet1
A | B | C |
---|---|---|
7413 | 4802227413 | |
7414 | 4802227414 | |
7419 | #N/A | |
7420 | #N/A | |
7425 | #N/A | |
7426 | #N/A | |
7427 | #N/A | |
7405 | 4802227405 | |
7406 | 4802227406 |
Sheet2
A | B | C |
---|---|---|
7413 | 4802227413 | |
7414 | 4802227414 | |
7406 | 4802227406 | |
7420 | 4802227420 | |
7405 | 4802227405 | |
7426 | 4802227426 | |
7427 | 4802227427 | |
7405 | 6025557405 | |
7406 | 6025557406 |
Example Output
A | B | C |
---|---|---|
7413 | 4802227413 | |
7414 | 4802227414 | |
7419 | #N/A | |
7420 | #N/A | |
7425 | #N/A | |
7426 | #N/A | |
7427 | #N/A | |
7405 | 4802227405 | 6025557405 |
7406 | 4802227406 | 6025557406 |
This is what you need to do, to accomplish the desired output :
=IFERROR(INDEX($F$3:$F$11,MATCH(1,(B3<>$F$3:$F$11)*(A3=--RIGHT($F$3:$F$11,4)),0)),"")
Alternative methods if applicable to excel versions:
=IFNA(FILTER($F$3:$F$11,((B3<>$F$3:$F$11)*(A3=--RIGHT($F$3:$F$11,4))),""),"")
=IFNA(XLOOKUP(1,((B3<>$F$3:$F$11)*(A3=--RIGHT($F$3:$F$11,4))),$F$3:$F$11,""),"")
NOTE: Change ranges and cell references as per your suit.
In Reference to the context of the post:
=IFERROR(INDEX(Sheet2!B:B,MATCH(1,(B3<>Sheet2!B:B)*(A3=--RIGHT(Sheet2!B:B,4)),0)),"")
Updated with additional formulas:
As per comments from OP:
Thank you that worked!! One more question, what would I need to change if I wanted to find a 3rd for 4th number that matches 7405 and have it show in column D and or column E
=IFERROR(INDEX($F$3:$F$11,
AGGREGATE(15,7,(ROW($F$3:$F$11)-ROW($F$3)+1)/
((B3<>$F$3:$F$11)*(A3=--RIGHT($F$3:$F$11,4))),
COUNTIFS(A$3:A3,A3))),"")