Forum Discussion
Solved
Help needed.
Dear Hans or Excel Expert,
I will need your advice on the Excel formula based on the attached Excel table by either;
1. Use VLOOKUP or another Excel function to match the first 3 digits of the product code in "Item Code" column C against 3 digits in the "Match code" in column E. (e.g. 789 --> OneAS (789))
2. Return the match "Product Category" code in column D to "Return value" in column F.
Show me the result in the chart too. I have provided an example of the return value.
Appreciate your help in advance.
Link: https://we.tl/t-t9BHvBFNoh
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.
