How to Use INDEX and MATCH to Populate an Excel Table Automatically
Question details
The user needs a reliable way to automatically populate a two-way data table by looking up values based on matching both row and column headers.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Populating a structured grid or table from a master dataset, requiring a formula that returns the exact value at the intersection of a matched row and column.
- Observed behavior
- Requires a dynamic formula, such as INDEX and MATCH, to be entered in the top-left cell and filled down and across the output table while handling missing data smoothly.
Ensure your source data is organized with clear, unique row and column headers, and verify that the lookup headers in your output table exactly match the text in your source dataset to avoid matching errors.
Use INDEX, MATCH, and IFERROR (Compatible with all versions)
This is the most reliable method for performing two-way lookups and automatically filling tables across all versions of Excel.
The INDEX function retrieves the value at a specific row and column intersection within a range. By nesting two MATCH functions inside INDEX, you can dynamically find the correct row and column based on your headers. Wrapping the entire formula in IFERROR ensures that missing data is displayed as a blank cell rather than an #N/A error.
Click on the top-left cell of your target output table where you want the first result to appear (e.g., cell H2).
Type the formula: =IFERROR(INDEX($C$2:$E$5,MATCH($G2,$B$2:$B$5,0),MATCH(H$1,$C$1:$E$1,0)),"") and adjust the ranges according to your specific dataset.
Press Enter to execute the formula. Click the fill handle in the bottom-right corner of the cell and drag it down to the last row of your table.
With the filled column selected, drag the fill handle to the right across all necessary columns to populate the entire output table.
Use the FILTER Function (Microsoft 365)
A modern array-based approach that automatically spills dynamic results across the output range.
Master Complex Formulas Easily with WPS Spreadsheet
WPS Spreadsheet fully supports advanced functions like INDEX, MATCH, VLOOKUP, and IFERROR. It provides a highly compatible, fast, and free alternative to easily handle large datasets and two-way lookups.
- 1. Open your workbook in WPS: Launch WPS Office, open the Spreadsheet application, and load your existing Excel data.
- 2. Apply your lookup formula: Select the target cell and enter the identical INDEX and MATCH formula you would use in Microsoft Excel.
- 3. Drag to populate: Use the smart fill handle to drag the formula across your table; WPS Spreadsheet will instantly calculate and correctly fill the grid.
Frequently Asked Questions
Why does my INDEX and MATCH formula return an #N/A error?
This error occurs when the lookup value does not exactly match the row or column header in your source data. Check for trailing spaces, typos, or mismatched data types (e.g., text vs. numbers). Using the IFERROR function wrapped around your formula will hide these errors and display a blank cell instead.
Can I use VLOOKUP instead of INDEX and MATCH for a two-way lookup?
Yes, you can use VLOOKUP combined with MATCH for two-way lookups (e.g., =VLOOKUP(row_val, range, MATCH(col_val, headers, 0), FALSE)). However, INDEX and MATCH is generally preferred as it is more robust, processes faster on large datasets, and doesn't break if you insert or delete columns.
What does the 0 mean in the MATCH function?
The 0 in the MATCH function specifies the match type as an 'exact match'. This forces Excel to look for a value that is exactly identical to your lookup value. If it cannot find an exact match, it returns an error.
How do dynamic array formulas like FILTER differ from INDEX and MATCH?
Dynamic array formulas automatically 'spill' their output into neighboring empty cells, meaning you only need to type the formula in one single cell. Standard INDEX and MATCH formulas only calculate for a single cell, requiring you to drag and copy the formula across the entire table.
