Help needed.

[Louis_yap]

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

[#2]

@Louis_yap 

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.

[#3]

@HansVogelaar 

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.

[#4]

=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.

[#5 · Accepted solution]

@Louis_yap 

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.

[#6]

@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.