How to Look Up Parent Company IDs in Excel When Names Repeat
Question details
The user needs to automatically look up and return unique IDs for parent companies from a master list, handling cases where the parent company names repeat in the target column.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Automating data entry for a project with over 100 rows by matching duplicate parent company names to a unique master list to retrieve their corresponding IDs.
- Observed behavior
- Manual lookup is too time-consuming. The goal is to use a formula to automatically fetch the correct ID for each parent company despite duplicate entries.
Ensure your master list of companies and their unique IDs contains no duplicates and is organized in clear, separate columns before applying the lookup formula.
Use INDEX and MATCH to Look Up IDs
The most robust way to look up values based on a search criterion, especially when the return column is to the left of the lookup column.
The INDEX and MATCH combination is highly flexible. MATCH finds the position of the parent company in your unique list, and INDEX retrieves the corresponding ID from that exact position. Duplicate parent names in your target column are not a problem because each row independently searches the unique company list.
Locate your unique ID column (e.g., A2:A14), your unique company name column (e.g., B2:B14), and the column where you want to look up the repeating parent companies (e.g., C2).
Click on the cell where you want the first ID to appear and type =INDEX($A$2:$A$14,MATCH(C2,$B$2:$B$14,0)).
Ensure you use the dollar signs ($) for your master list ranges ($A$2:$A$14 and $B$2:$B$14) so they don't shift when you copy the formula down.
Press Enter to get the first result, then click the fill handle at the bottom-right of the cell and drag it down to apply the formula to all remaining rows.
Use VLOOKUP (If IDs are to the right of Company Names)
A simpler alternative if your master list is structured with the Company Name in the first column and the ID in the second column.
Easily Perform Advanced Lookups with WPS Spreadsheet
WPS Spreadsheet fully supports advanced lookup functions like INDEX, MATCH, VLOOKUP, and XLOOKUP, making it incredibly easy to manage large datasets and automate repetitive tasks.
- 1. Open your dataset in WPS: Launch WPS Spreadsheet and open your workbook containing the company data.
- 2. Input the lookup formula: Select the target cell and enter your =INDEX(..., MATCH(...)) formula just as you would in Excel.
- 3. Use the Fill Handle: Double-click the small square at the bottom right of the active cell to instantly fill the formula down to the end of your data.
Frequently Asked Questions
Why is my INDEX MATCH formula returning an #N/A error?
An #N/A error usually means the parent company name in your target cell does not exactly match any name in your unique master list. Check for hidden spaces, trailing spaces, or spelling differences.
Can I use XLOOKUP instead of INDEX and MATCH?
Yes, if you are using a newer version of Excel or WPS Office, you can use =XLOOKUP(C2, $B$2:$B$14, $A$2:$A$14). It is simpler to write and automatically defaults to an exact match.
Why do my cell references change when I drag the formula down?
This happens if you use relative references. You must lock your master list ranges by adding dollar signs (e.g., $A$2:$A$14) to make them absolute references before dragging the formula down.
Does capitalization matter when using MATCH or VLOOKUP?
No, standard lookup functions in Excel and WPS Spreadsheet are not case-sensitive. 'Company A' will successfully match with 'COMPANY A'.
