'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Match Products to SKUs in Excel using INDEX and MATCH
Question details
The user needs to retrieve a specific product name that corresponds to a given SKU using Excel formulas, and may be encountering a #NAME? error.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Looking up product details from an inventory list based on a matching SKU code.
- Observed behavior
- The user wants to find the correct lookup formula to match data, and needs to resolve a #NAME? error caused by misspelled, unsupported functions, or incorrect separators.
Before applying your formula, ensure that your SKU numbers are formatted consistently (either all text or all numbers) in both the source list and the lookup cell to prevent mismatched #N/A errors.
Use INDEX and MATCH to Find Product Names
This classic combination works across all versions of Excel and is highly flexible, allowing you to look up data to the left or right of your target column.
The MATCH function finds the position of your SKU in a list, and the INDEX function retrieves the product name from that exact position.
Locate the column containing your SKUs (e.g., A2:A100) and the column containing your product names (e.g., B2:B100).
Click on the cell where you want the matched product name to be displayed (for example, E2).
Type the formula =INDEX($B$2:$B$100,MATCH(D2,$A$2:$A$100,0)) where D2 is the specific SKU you are searching for. The '0' in the MATCH function ensures an exact match.
Press Enter to see the result. You can then drag the fill handle down to apply this formula to other SKUs.
Use XLOOKUP (For Newer Excel Versions)
If you are using Microsoft 365 or Excel 2021 and newer, XLOOKUP provides a simpler and more robust way to match SKUs to products.
Troubleshoot the #NAME? Error
A #NAME? error indicates that Excel does not recognize text in the formula. This is usually due to a typo or a compatibility issue.
Effortlessly Look Up Data with WPS Spreadsheet
WPS Spreadsheet fully supports advanced lookup functions, including INDEX, MATCH, and the modern XLOOKUP. It is a seamless, highly compatible tool for managing your inventory and matching SKUs without errors.
- 1. Open your data file: Launch WPS Spreadsheet and open the inventory file containing your SKUs and product names.
- 2. Select your cell: Click on the cell where you want to retrieve the matched product data.
- 3. Input the formula: Type in your preferred formula, such as =XLOOKUP(D2, A2:A100, B2:B100).
- 4. Apply to all rows: Hit Enter, then drag the bottom-right corner of the cell down to automatically match the rest of your SKUs.
Frequently Asked Questions
Why does my INDEX and MATCH formula return #N/A?
The #N/A error means the lookup value (SKU) could not be found in the lookup array. This often happens if there are trailing spaces in your data or if numbers are formatted as text in one column but as numbers in the other. Using the TRIM function can help clean your data.
Can I use INDEX and MATCH to look up data to the left of the SKU?
Yes. Unlike VLOOKUP, which only searches from left to right, INDEX and MATCH can retrieve data in any column, regardless of whether it is located to the left or the right of the lookup column.
Is XLOOKUP available in older versions of Excel?
No, XLOOKUP is only available in Microsoft 365, Excel 2021, and newer versions. If you are using Excel 2019 or earlier, you will get a #NAME? error and should use INDEX and MATCH instead.
What does the '0' mean at the end of the MATCH formula?
The '0' specifies the match type. It tells the MATCH function to find the exact value. If you omit it, the formula defaults to an approximate match, which can return incorrect product names for your SKUs.
