How to Use INDEX and MATCH for Horizontal Data Entry in Excel
Question details
The user wants to create a spreadsheet that auto-fills related information across multiple columns when a specific code is entered horizontally.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Setting up an autocomplete or lookup system where entering a code retrieves corresponding data from a master list into adjacent cells.
- Observed behavior
- Needs a formula combination to locate the entered code in a source table and extract the correct values across horizontal destination cells.
Ensure your master data table is organized with the lookup codes in a single row or column, and identify the exact cell ranges before writing your formulas.
Combine INDEX and MATCH to Return Horizontal Data
Use the MATCH function to find the position of the entered code, and the INDEX function to retrieve the related data from that position in the master list.
The INDEX and MATCH combination is highly flexible for autocomplete tasks. MATCH locates the relative row or column position of your lookup value, while INDEX uses that position to fetch the corresponding data from another column or row.
Select the destination cell where you want the related information to appear (e.g., C5). Note the cell where the user enters the code (e.g., B5) and the range of your master data.
Type =MATCH(B5, MasterList!A:A, 0) to find the exact row number where the entered code exists in your master list's code column.
Combine the formulas by typing =INDEX(MasterList!B:B, MATCH(B5, MasterList!A:A, 0)). This returns the value from column B in the master list that corresponds to the found row.
Lock the lookup value reference by changing B5 to $B5, and drag the formula to adjacent horizontal cells. Update the INDEX return array (e.g., change MasterList!B:B to MasterList!C:C) for each new column.
Effortlessly Perform Data Lookups with WPS Spreadsheet
WPS Spreadsheet offers full support for advanced functions like INDEX, MATCH, XLOOKUP, and VLOOKUP. It provides a familiar interface to process complex horizontal data entry and automate your workflows without any subscription fees.
- 1. Open WPS Spreadsheet: Launch WPS Office and create a new blank spreadsheet or open your existing Excel file.
- 2. Insert your data: Set up your master table with the reference codes and the related data columns you want to extract.
- 3. Apply the INDEX/MATCH formula: Click on the formula bar, type =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)), and press Enter.
- 4. Drag to auto-fill: Drag the fill handle horizontally to populate adjacent cells with the corresponding data.
Frequently Asked Questions
Why does my INDEX and MATCH formula return an #N/A error?
An #N/A error typically occurs because the MATCH function cannot find the lookup value in the specified lookup array. Check for trailing spaces, formatting differences (like text vs. numbers), or typos in your source data.
Can I look up values horizontally across columns instead of rows?
Yes, you can use MATCH to find a column number instead of a row number. For example, =MATCH(lookup_value, A1:Z1, 0) will find the position across a horizontal header row, which you can then pass into the column_num argument of the INDEX function.
Is INDEX and MATCH better than VLOOKUP?
INDEX and MATCH is generally more flexible than VLOOKUP because it allows you to look up data to the left of your lookup column and is more robust if columns are inserted or deleted in your source data without breaking the formula.
