How to Use Excel INDEX MATCH to Return Supervisor and Manager Names
Question details
The user needs to match an employee ID in one column against a lookup table to accurately return the corresponding supervisor and manager names.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Looking up and returning multiple related values from a separate data table based on a unique identifier.
- Observed behavior
- The system successfully looks up the matched ID and populates the supervisor and manager columns, returning blank cells when no match is found.
Ensure that your lookup table contains unique IDs without duplicates, and that the columns containing the lookup IDs and the corresponding supervisor and manager names are clearly identified.
Use Standard INDEX MATCH Formulas to Return Names
This is the most direct method to look up and return supervisor and manager names based on a unique ID.
The INDEX MATCH combination is highly flexible for data retrieval. Unlike VLOOKUP, it allows you to look up values even when your return column is to the left or right of your search column.
Click on the first cell in your Supervisor column (e.g., cell G2) and enter =INDEX(K:K,MATCH($A2,J:J,0)). In this example, column K contains the supervisor names and column J contains the lookup IDs.
Click on the first cell in the Manager column (e.g., cell H2) and enter =INDEX(L:L,MATCH($A2,J:J,0)). Here, column L contains the manager names.
Select cells G2 and H2, then double-click or drag the fill handle down to copy the formulas for all corresponding rows.
Wrap Formulas in IFERROR to Hide Missing Matches
Use this approach to keep your spreadsheet clean by returning a blank cell instead of an #N/A error when an ID does not exist in the lookup table.
Easily perform advanced lookups using WPS Spreadsheet
WPS Spreadsheet fully supports advanced functions like INDEX, MATCH, and IFERROR, making it simple to build robust lookup tables for your employee data with zero compatibility issues.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your workbook containing the employee and lookup tables.
- 2. Input the INDEX MATCH formula: Select the target cell, type =INDEX(K:K,MATCH($A2,J:J,0)) to pull the required data, and press Enter.
- 3. Drag to fill: Use the intuitive fill handle to drag and copy the formula across all necessary rows instantly.
Frequently Asked Questions
Why is my INDEX MATCH formula returning an #N/A error?
The #N/A error occurs when the MATCH function cannot find the lookup value (the ID) in your lookup array. Ensure the ID exists in your source table, there are no trailing spaces, and the data types perfectly match (e.g., both are formatted as text or numbers).
Can I use VLOOKUP instead of INDEX MATCH for this task?
Yes, if the lookup ID is in the leftmost column of your lookup table, you can use VLOOKUP. However, INDEX MATCH is generally preferred as it is more flexible, allowing the ID column to be located anywhere in the dataset without breaking when columns are inserted or deleted.
Should I put my lookup table on a separate worksheet?
Yes, it is highly recommended to place your lookup table on a separate worksheet. This keeps your main dataset clean and allows the lookup table to be sorted or modified independently without affecting your active data entry view.
