Forum Discussion
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
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")
- Kyle555Copper Contributor
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
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")
- Kyle555Copper ContributorI will try this tomorrow thank you