SOLVED

# Help needed.

Copper Contributor

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

5 Replies

# Re: Help needed.

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.

# Re: Help needed.

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.

# Re: Help needed.

=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.
best response confirmed by Louis_yap (Copper Contributor)
Solution

# Re: 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.

# Re: Help needed.

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

1 best response

Accepted Solutions
best response confirmed by Louis_yap (Copper Contributor)
Solution

# Re: 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.