How to Automatically Fill Data from a Master Lookup Table in Excel
Question details
The user needs a way to automatically populate static attributes (like group or category) for individuals in monthly worksheets without retyping them every time.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Managing monthly spreadsheets where people's names and dates are entered, but their corresponding group and category information must be pulled automatically.
- Observed behavior
- Currently, group and category data is missing in monthly sheets, requiring manual data entry or a streamlined automated formula approach to pull this data from a central list.
Ensure that names entered in your monthly worksheets exactly match the names in your master list, as extra spaces or misspellings will cause lookup formulas to return errors.
Create a Master Lookup Table and Use VLOOKUP or XLOOKUP
Use a dedicated master list formatted as an Excel Table combined with lookup formulas to automatically pull categorized data.
Formatting your ranges as official Excel Tables is the most efficient way to manage repetitive data. It ensures that whenever you add new people, your lookup formulas automatically copy down to new rows without manual dragging.
Open a new worksheet in your workbook, name it 'Master List', and enter your unique list of people, their respective groups, and categories.
Select your master list data, press Ctrl+T (or go to Insert > Table), and check 'My table has headers'. In the Table Design tab, rename the table to 'Persons'.
Go to your monthly worksheet, select the data range containing the names and dates, and press Ctrl+T to convert it into a Table as well.
In the Group column of your monthly sheet, enter `=XLOOKUP([@Name], Persons[Name], Persons[Group], "Not Found")` or `=VLOOKUP([@Name], Persons, 2, FALSE)`. Press Enter, and the formula will automatically apply to the entire column.
Automatically Fill Data with WPS Spreadsheet
WPS Spreadsheet fully supports advanced lookup functions like VLOOKUP and XLOOKUP, as well as dynamic tables. You can easily build master lookup lists to automate your monthly tracking sheets.
- 1. Open your Workbook in WPS: Launch WPS Spreadsheet and open your monthly tracking workbook.
- 2. Build the Master List: Create a new sheet, input your unique names and categories, select the range, and click 'Insert' > 'Table'.
- 3. Insert the Lookup Function: In your monthly sheet, click on the target cell, go to the 'Formulas' tab, select 'Lookup and Reference', and choose VLOOKUP or XLOOKUP.
- 4. Define the Formula Arguments: Select the name cell as the lookup value, your master table as the array, and output the desired category column. Press Enter to auto-fill.
Frequently Asked Questions
Why is my VLOOKUP or XLOOKUP formula returning an #N/A error?
An #N/A error means the formula cannot find an exact match in the master list. Check for trailing spaces, spelling differences, or ensure the lookup value actually exists in the master table.
How do I update the master lookup table when a person changes groups?
Simply navigate to the sheet containing your master table and update the value in the group column. All monthly sheets using XLOOKUP or VLOOKUP connected to that table will automatically display the updated group.
Can I use VLOOKUP if the name isn't the first column in the master table?
No, VLOOKUP requires the lookup value to be in the leftmost column of the table array. If the name is not in the first column, you should use XLOOKUP or an INDEX and MATCH combination instead.
Do I need to lock the cell references in my formula?
If you format your data as an Excel Table (using Ctrl+T), it uses structured references (like Persons[Name]) which automatically adjust and do not need locking. If you use standard cell ranges (like A2:B100), you must lock them using absolute references (like $A$2:$B$100) by pressing F4.
