How to Return Match Table Values in Excel using INDEX and MATCH
Question details
The user needs to retrieve specific text from a two-dimensional match table and display it in a fixed-layout result table.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Extracting data from a structured match table based on specific row and column criteria.
- Observed behavior
- The user wants to return exact cell values using lookup formulas instead of relying on cell fill colors, keeping the result table's arrangement intact.
Ensure that your match table has clear, unique row labels and column headers, as your formulas will use these text values to identify the correct data instead of relying on visual cell highlighting.
Use INDEX and MATCH for Two-Dimensional Lookups
Utilize the INDEX and MATCH functions to precisely locate and retrieve data from a 2D table based on your row and column keys.
Standard Excel formulas cannot inherently read manual cell highlighting (fill color). Instead, you should rely on the exact cell values (row and column keys) to perform a reliable two-dimensional lookup.
By nesting two MATCH functions inside an INDEX function, you can pinpoint the exact row and column intersection dynamically.
Determine your ResultRange (the data grid containing the text to return), RowLabels (the vertical headers), and ColumnHeaders (the horizontal headers).
Click on the specific cell in your fixed-layout result table where you want the retrieved text to appear.
Type the formula: =INDEX(ResultRange,MATCH(RowKey,RowLabels,0),MATCH(ColumnKey,ColumnHeaders,0)) replacing the placeholder names with your actual cell references (e.g., A2:F10).
Press Enter to return the matched value. Change the row or column criteria in your fixed table to verify that the returned value updates accordingly.
Perform Advanced Lookups Easily with WPS Spreadsheet
WPS Office provides powerful data analysis tools and full support for advanced lookup functions like INDEX, MATCH, and XLOOKUP. You can seamlessly manage complex match tables with high precision and full formula compatibility.
- 1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing your match table.
- 2. Select the output cell: Click the cell in your fixed-layout table where the result should be displayed.
- 3. Insert the lookup formula: Go to the Formulas tab, select Insert Function to find INDEX and MATCH, or simply type the two-dimensional lookup formula directly into the formula bar.
- 4. Retrieve the data: Input your row and column criteria arrays, then press Enter to retrieve your exact match instantly.
Frequently Asked Questions
Can I use a formula to return a value based on cell color in Excel?
Native Excel formulas cannot read cell formatting like fill colors. You either need to use a VBA macro or base your lookup on the actual data values using formulas like INDEX and MATCH.
What is the alternative to INDEX and MATCH for 2D lookups?
If you are using a newer version of Excel or WPS Spreadsheet, you can use nested XLOOKUP functions to perform a two-dimensional lookup more intuitively without needing the INDEX function.
Why does my MATCH formula return an #N/A error?
An #N/A error usually means the exact lookup value was not found in the lookup array. Check for trailing spaces, incorrect spelling, or mismatched data types (such as text formatted as numbers) in your labels.
How do I lock the table array so I can drag the formula down?
Use absolute references by pressing F4 when selecting your ResultRange, RowLabels, and ColumnHeaders (e.g., $A$2:$F$10). This prevents the cell references from shifting when you copy the formula to other rows or columns.
