excelexcel-formula

Find all matches that use the 4 digits in sheet1 to find the 10 digit phone numbers by matching the last 4 numbers in sheet2


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

Solution

  • This is what you need to do, to accomplish the desired output :

    enter image description here


    =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


    enter image description here


    =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))),"")