How to Populate an Excel Table Based on Values in Other Cells

When managing large datasets, manually typing corresponding information into adjacent columns is inefficient and highly prone to data entry errors. If you want to understand populating an Excel Table Based on Values in Other Cells, the most effective approach relies on utilizing dynamic lookup functions or conditional logic. By setting up these formulas, you create an automated workflow where typing a single unique identifier—such as a product SKU, employee ID, or transaction number—instantly pulls all related data from a master database and displays it in your current working table. This ensures your spreadsheets remain accurate, automatically updated, and significantly faster to process.

Using VLOOKUP to Auto-Fill Table Data

The VLOOKUP (Vertical Lookup) function remains the most widely recognized method for retrieving data across spreadsheets. It searches for a specific value in the first column of a designated data range and returns a value in the same row from a column you specify. This method is ideal when your master data is organized vertically and the lookup identifier is located in the leftmost column of that master table.

Follow these precise steps to construct your formula:

• Step 1: Open your .xlsx workbook and ensure your master data is organized as a structured table or a continuous range. For example, place your Product IDs in column A, Product Names in column B, and Prices in column C.
• Step 2: Navigate to your destination table. Click inside the exact cell where you want the retrieved data to appear (e.g., cell E2 for the Product Name).
• Step 3: Type =VLOOKUP( into the formula bar to initiate the function.
• Step 4: Click the cell containing your lookup value in the destination table (e.g., D2, where you will type the Product ID). Type a comma to proceed to the next argument.
• Step 5: Highlight your entire master data range (e.g., Sheet2!A2:C100). Press the F4 key immediately to lock this range into absolute references (it will change to $A$2:$C$100). Type another comma.
• Step 6: Type the column index number that contains your target data. Because Product Name is the second column in your master range, type 2. Type a comma.
• Step 7: Type FALSE to force an exact match. Type the closing parenthesis ) and press Enter.

To verify the workflow, type a known Product ID into cell D2. Cell E2 will immediately populate with the corresponding Product Name. You can now drag the fill handle at the bottom-right corner of cell E2 downward to apply this automation to the rest of your table.

Applying XLOOKUP for Flexible Data Retrieval

While VLOOKUP is a standard tool, it possesses a strict limitation: it can only search the leftmost column of a table and move right. If your reference identifiers are located in the middle of your master dataset, you must use XLOOKUP. This modern function processes distinct arrays for the search and the return, allowing it to look left, look right, and handle missing data natively without requiring complex error-handling combinations.

To populate your table using XLOOKUP:

1. Click the destination cell in your working table.
2. Type =XLOOKUP( to begin.
3. Select the reference cell containing the identifier you want to look up, followed by a comma.
4. Navigate to your source data and highlight only the specific column containing the matching identifiers (the lookup array). Press F4 to lock the range. Type a comma.
5. Highlight the specific column containing the data you want to pull into your destination cell (the return array). Press F4 to lock this range as well.
6. Optional: Type a comma and enter a text string like "Not Found" in quotation marks. This defines what the cell should display if the identifier does not exist in the master database.
7. Press Enter to execute the function.

Test the formula by entering an identifier that is not in your database. The cell should cleanly display "Not Found" rather than an abrupt #N/A error, proving the formula's built-in error handling is active.

Populating Cells Based on Categorical Conditions Using IF

If you need to populate a table based on logical thresholds rather than searching a master database, the IF function is the appropriate tool. This method evaluates a condition in another cell and populates your target cell with specific text, numbers, or calculations depending on whether that condition is true or false.

For example, to populate a "Bonus Status" column based on a sales figure:

• Click the target cell in your status column.
• Type =IF( to start the logic statement.
• Select the cell containing the sales figure, and type your logical test (e.g., B2>5000). Type a comma.
• Define the value to populate if the condition is met by typing "Eligible". Type a comma.
• Define the value to populate if the condition fails by typing "Review Required".
• Type the closing parenthesis and press Enter.

Change the sales figure in cell B2 from 4000 to 6000. The target cell will dynamically switch from "Review Required" to "Eligible," confirming the logical threshold is functioning properly.

Managing Dynamic Spreadsheets with WPS Office

Users handling large datasets often require a lightweight spreadsheet environment that processes complex lookup arrays rapidly. WPS Office provides native, seamless support for many commonly used data-retrieval formulas, including VLOOKUP, XLOOKUP, and nested IF statements. If you are configuring these automated tables without an active Microsoft 365 subscription, WPS Spreadsheet allows you to execute the exact same syntax and logic.

To set up an automated lookup table in WPS Spreadsheet:

1. Launch WPS Office and open your spreadsheet file containing the raw data.
2. If you prefer visual assistance over typing formulas manually, select your destination cell and click the Formulas tab on the upper ribbon.
3. Click the Insert Function button (represented by an fx icon).
4. In the dialog box, search for your preferred method (e.g., XLOOKUP) and click OK.
5. The Function Arguments window will appear. Click the selection arrows next to each field to highlight your Lookup_value, Lookup_array, and Return_array directly on the sheet.
6. Click OK to finalize.

Because WPS Office utilizes identical formula architectures, files saved in WPS will retain full functionality when shared with colleagues using other standard spreadsheet software. Your automated tables will continue to populate correctly regardless of the application used to open the file.

Use Word, Excel, and PPT for FREE

Free Download

Frequently Asked Questions

Why does my lookup formula return an #N/A error when the matching value clearly exists in the source table?

This is almost always caused by a data formatting mismatch between the lookup cell and the source table. If your identifier is formatted as a Number in the destination table but stored as Text in the master data (often indicated by a small green triangle in the corner of the cell), the formula will not recognize them as a match. Select both columns, navigate to the Data tab, and use the Text to Columns tool—clicking Finish immediately—to force both columns into the same standard format.

Can I populate multiple adjacent columns using a single formula?

Yes. If you are using XLOOKUP, you can highlight multiple adjacent columns for your return array (e.g., highlighting columns B, C, and D). The function will automatically "spill" the corresponding data across the adjacent cells in your destination table. If you are using VLOOKUP, you must write the formula in the first cell, lock the column reference of the lookup value (e.g., $A2), copy the formula to the right, and manually change the column index number for each subsequent cell.

How do I extract data from a table located in a completely different workbook?

You can reference external workbooks by keeping both the destination file and the source file open simultaneously. Start typing your VLOOKUP or XLOOKUP formula in the destination workbook. When it is time to select the array, use your taskbar to switch to the source workbook and highlight the data range. Excel or WPS Spreadsheet will automatically write the complete file path, including the workbook name in square brackets, into the formula bar. Press Enter to lock the external link.

What should I do if my lookup identifier is in the middle of a text string instead of being an exact match?

You can use wildcard characters within your VLOOKUP or XLOOKUP formulas to find partial matches. In your formula's lookup value argument, wrap your cell reference with asterisks using the ampersand symbol. For example, instead of selecting just cell A2, type "*"&A2&"*". This instructs the spreadsheet to search the master table for any cell that contains the value of A2 anywhere within its text, allowing you to populate data based on partial string matches.