Excel INDEX MATCH Alternative: Return a GL Code Using XLOOKUP
Question details
The user wants to match a category on one worksheet with a category list on another worksheet to retrieve the corresponding GL code, looking for a simpler alternative to the complex INDEX MATCH formula.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Looking up and returning specific financial data (GL codes) from another worksheet based on corresponding category matches.
- Observed behavior
- Needs an efficient formula, such as XLOOKUP, to handle cross-sheet data retrieval without encountering the syntax complexities of traditional lookup methods.
Ensure both your main worksheet and the reference worksheet containing the GL codes are open, and format your reference data as a Table (e.g., TableGL) to make writing the formula easier and more dynamic.
Use XLOOKUP to Retrieve the GL Code
XLOOKUP is a modern, straightforward alternative to INDEX MATCH that allows you to easily search for a category and return its corresponding GL code from another sheet or table.
XLOOKUP simplifies the lookup process by only requiring you to specify what you are looking for, where to look, and what to return. It also has built-in error handling for missing values.
Click on the cell in your main worksheet where you want the GL code to be displayed.
Type the formula: =IF(C2="","",XLOOKUP(C2,TableGL[CATEGORY],TableGL[GL CODE],"No match")). Replace 'C2' with the cell containing your lookup category.
If your source data is formatted as a table, use the structured reference for the category column (like TableGL[CATEGORY]) instead of a standard cell range. Press Enter to apply.
Use Traditional INDEX MATCH (For Older Excel Versions)
If you are using a version of Excel that does not support the newer XLOOKUP function, the traditional INDEX and MATCH combination is the standard method for left-to-right or right-to-left lookups.
Use XLOOKUP and Advanced Formulas in WPS Spreadsheet
WPS Spreadsheet fully supports advanced modern Excel functions, including XLOOKUP, making cross-sheet data retrieval and financial tracking effortless and fast.
- 1. Open your workbook in WPS: Launch WPS Office and open your spreadsheet file containing the category list and GL code sheets.
- 2. Format data as a Table: Select your GL code reference range, navigate to the Home tab, and click 'Format as Table' to easily name your ranges.
- 3. Insert the XLOOKUP formula: In your main sheet, type =XLOOKUP() and use the intuitive tooltip guide to select your lookup value, lookup array, and return array.
- 4. Drag to fill: Click the small square at the bottom-right of the active cell and drag down to apply the formula to the rest of the column.
Frequently Asked Questions
Why does my XLOOKUP formula return a #NAME? error?
This error usually occurs if you are using an older version of Excel that does not support the XLOOKUP function. In this case, use the traditional INDEX MATCH combination instead, or switch to a modern spreadsheet software like WPS Office.
What is the advantage of XLOOKUP over INDEX MATCH?
XLOOKUP is much simpler to write and read. It automatically allows you to specify a 'not found' message directly within the formula without needing an extra IFERROR function, and it defaults to exact matches, unlike older lookup formulas.
Can I use XLOOKUP to return multiple columns at once?
Yes, if you select multiple adjacent columns as your return array (for example, returning both the GL Code and the Account Description), XLOOKUP will automatically spill the results into the adjacent cells.
