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...
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")
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")