Forum Discussion

Kyle555's avatar
Kyle555
Copper Contributor
Jun 14, 2023

Help with an excel formula.

Hello, 

 

I have 2 tables on different sheets both containing mobile numbers in colA. (Table 2 has less phone numbers than table 1)

 

Table 1 has user name in col C and address in colD. 

Table 2 has user name in col D and address in col E. 

 

I need to compare table 1s mobile numbers to table 2 mobile numbers.

 

If the mobile number appears anywhere in table 2 I need it to automatically copy and past the user and address into the correct place in table 1. 

If the mobile number isn't in table 2 it can just return "UNKNOWN". 

 

SO far I have this formula which returns "UNKNOWN" or "TRUE" but I cannot figure out how to change "TRUE" to the appropriate user and address.

=IF(ISNA(VLOOKUP(A2,Sheet2!$A$2:$A$100,1,0)),"UNKNOWN","TRUE").

 

Thank you for any help.

 

4 Replies

  • Kyle555 

    If you have Excel in Office 2019 or earlier:

     

    =IFERROR(VLOOKUP(A2,Sheet2!$A$2:$E$100,4,FALSE),"UNKNOWN")

    and

    =IFERROR(VLOOKUP(A2,Sheet2!$A$2:$E$100,5,FALSE),"UNKNOWN")

     

    If you have Excel in Microsoft 365 or Office 2021:

     

    =XLOOKUP(A2,Sheet2!$A$2:$A$100,Sheet2!$D$2:$E$100,"UNKNOWN")

    • Kyle555's avatar
      Kyle555
      Copper Contributor

      HansVogelaar 

      Hi, this works perfectly thank you. 

      I am using the XLOOKUP. 

       

      In table 2 there are multiple results and names for the each phone number. It has a date in colG. Is there an easy way to have it check the date for each number and use the latest most update details ? 

       

      Cheers 

      • Kyle555 

        Try this:

         

        =IFERROR(INDEX(Sheet2!$D$2:$E$100, MATCH(1, (Sheet2!$A$2:$A$100=A2)*(Sheet2!$G$2:$G$100=MAXIFS(Sheet2!$G$2:$G$100, Sheet2!$A$2:$A$100, A2)), 0), 0), "UNKNOWN")

    • Kyle555's avatar
      Kyle555
      Copper Contributor
      I will try this tomorrow thank you