Forum Discussion
Help needed.
XLOOKUP is only available in Excel in Microsoft 365 and Office 2021, not in older versions.
The first formula that I suggested should work in all versions of Excel.
In F2:
=INDEX($D$2:$D$22, MATCH(LEFT(C2, 3), $E$2:$E$22&"", 0))
or
=XLOOKUP(LEFT(C2, 3), $E$2:$E$22&"", $D$2:$D$22)
See the attached version.
Hi @hans_vogelaar,
Upon pasting the given formula =XLOOKUP(LEFT(C2, 3), $E$2:$E$22&"", $D$2:$D$22) into Cell F2, the displayed value is #NAME?. Could you kindly advise on the issue?
If I accidentally click on the formula given by the example document you provided, the returned value displays as #NAME?, even after clicking the Undo button, the data remains unretrievable. I am forced to close the entire spreadsheet and reopen it.
the formula provided {=_xlfn.XLOOKUP(LEFT(C12, 3), $E$2:$E$22&"", $D$2:$D$22)} is something new to me, as I'm not very fluent in Excel. Can you explain further how I should utilize and apply this technique to my actual data, I'd appreciate your insights on this.
Thank you in advance for your invaluable assistance.
XLOOKUP is only available in Excel in Microsoft 365 and Office 2021, not in older versions.
The first formula that I suggested should work in all versions of Excel.
HansVogelaar peiyezhu
Hi All,Appreciate the prompt replies,
I was using Excel 2016. I have tested the formula on Microsoft 365 Excel and it works now.
Again, thank you very much for the assistance.
- =INDEX($D$2:$D$22, MATCH(LEFT(C2, 3), $E$2:$E$22&"", 0))
Apply this formular if you are not using office365.
#NMAE means XLOOKUP is not function name.
