Function Problems

How to Use INDEX-MATCH with a Column Header and Row ID in Excel

John Wilson Sep 28, 2026 869 views

Question details

The user needs to retrieve a specific value from a data table by matching both a row ID and a specific column header.

How to Use INDEX-MATCH Using a Column Header and Row ID

Product: Spreadsheet
Device & OS: not provided
Scenario: Looking up dynamic values across rows and columns using a two-way match formula in a large dataset.
Observed behavior: Requires a nested formula that searches vertically for a row ID and horizontally for a column header to extract the intersecting value.

Before you start

Ensure that your lookup table has unique row IDs and distinct column headers. Remove any merged cells within the data range, as they can cause the INDEX-MATCH formula to return incorrect results.

Solution 1Recommended

Use a Two-Way INDEX-MATCH Formula

Combine the INDEX function with two MATCH functions to dynamically look up data intersecting at a specific row and column.

A standard INDEX-MATCH formula looks up data in a single column or row. By adding a second MATCH function, you can search both vertically and horizontally. The first MATCH finds the correct row based on the ID, while the second MATCH finds the correct column based on the header.

Select the target cell

Click on the cell where you want the retrieved value to appear (for example, cell C3).

Define the INDEX data range

Type the beginning of your formula to define the array where your final result lives: =INDEX($I$3:$Z$100,

Add the row MATCH function

Insert the first MATCH function to locate the row ID: MATCH(B3,$H$3:$H$100,0).

Add the column MATCH function

Insert the second MATCH function to locate the column header: MATCH("Total2",$I$2:$Z$2,0).

Combine and execute

Combine the elements into the final formula: =INDEX($I$3:$Z$100,MATCH(B3,$H$3:$H$100,0),MATCH("Total2",$I$2:$Z$2,0)). Press Enter to apply the formula, then drag the fill handle down to apply it to other rows.

Locking Cell References: Using absolute references (the dollar signs like $I$3:$Z$100) ensures that your lookup ranges stay locked in place when you copy or fill the formula down to other cells.

Advanced Data Lookup

Master Complex Data Lookups with WPS Spreadsheet

WPS Spreadsheet fully supports advanced array functions like INDEX and MATCH, allowing you to quickly process, look up, and analyze large datasets without experiencing software lag.

1. Open your data file: Launch WPS Spreadsheet and open the workbook containing your target tables.
2. Navigate to the Formulas tab: Click on the cell where you want to output the lookup result, then go to the 'Formulas' tab on the top ribbon.
3. Insert your functions: Click 'Insert Function' to build your formula using the dialog box, or simply type your nested INDEX-MATCH formula directly into the formula bar.
4. Calculate and fill: Press Enter to calculate the intersecting value, and drag the cell's bottom-right corner to fill the formula across your dataset.

100% compatibility with Microsoft Excel formulas like INDEX and MATCHFree, lightweight, and lightning-fast performance for large data tablesBuilt-in Formula Evaluator to easily trace and debug complex nested functionsCross-platform support for Windows, Mac, Linux, and Mobile devices

microsoft office alternative - wps office

Frequently Asked Questions

Why is my INDEX-MATCH formula returning an #N/A error?

The #N/A error usually happens if the exact lookup value (either the row ID or the column header) doesn't exist in the specified ranges. Double-check for hidden spaces, typos in your headers, or mismatched data types between the lookup value and the lookup array.

What does the '0' mean at the end of the MATCH function?

The '0' as the third argument in the MATCH function specifies an exact match. This tells the function to find the precise text or number of your row ID or column header, rather than settling for an approximate match.

Can I use VLOOKUP instead of INDEX-MATCH for a two-way lookup?

While VLOOKUP can perform a two-way lookup when combined with the MATCH function for the column index number, INDEX-MATCH is generally preferred because it is faster, more flexible, and doesn't break if you insert or delete columns in your data table.

Does WPS Spreadsheet fully support standard Excel formulas?

Yes, WPS Spreadsheet supports the exact same syntax for mathematical, logical, and lookup formulas (including INDEX and MATCH) as Microsoft Excel, ensuring seamless compatibility for all your spreadsheet tasks.

Excel & Data Problems