Function Problems

How to Use INDEX and MATCH to Return Multiple Columns by ID in Excel

Kushani Nimanthika Oct 1, 2026 868 views

Question details

The user wants to retrieve multiple columns of data from a secondary sheet based on a matching User ID in the primary sheet.

How to Return Data from Another Sheet Using INDEX and MATCH by ID

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Pulling associated records from columns B through P corresponding to a specific ID found in Column A without manually adjusting VLOOKUP indexes.
Observed behavior: Users need an efficient formula to return an entire row of related data dynamically, bypassing VLOOKUP limitations in Excel 2016 or using modern array formulas in newer versions.

Before you start

Ensure that your User IDs in both sheets are formatted identically (e.g., both stored as text or both as numbers) to prevent unexpected match errors.

Solution 1Recommended

Use INDEX and MATCH with Mixed References (Compatible with Excel 2016)

This method uses a combination of INDEX and MATCH with locked column and row references, allowing you to write the formula once and drag it across multiple columns easily.

By carefully applying absolute and relative references ($ symbols), you can instruct Excel to lock the lookup ID column while allowing the return columns to dynamically shift as you drag the formula to the right.

Select the destination cell

Click on the first empty cell in Sheet1 where you want the retrieved data to appear (e.g., cell D2).

Enter the INDEX and MATCH formula

Type the formula: =IFERROR(INDEX(Sheet2!B$2:B$4,MATCH($C2,Sheet2!$A$2:$A$4,0)),""). Ensure you adjust $C2 to the cell containing your target User ID, and update the ranges to match your actual dataset rows.

Fill the formula across columns

Press Enter to apply the formula. Then, click the fill handle (the small green square at the bottom-right corner of the cell) and drag it to the right across the remaining columns (e.g., up to column P).

Fill the formula down the rows

Keep the newly filled row of formulas selected, click the fill handle again, and drag it down to apply the lookup logic to all other User IDs in your list.

Use INDEX and MATCH with Mixed References (Compatible with Excel 2016)

Understanding Mixed References: Notice the $ sign placement in the formula. $C2 locks the column so MATCH always looks at your ID column, while B$2:B$4 allows the column to shift to C, D, etc., as you drag right.

Solution 2

Use the FILTER Function to Spill Data Automatically

In newer versions of Excel, the FILTER function can dynamically return an entire range of columns at once without needing to drag the formula across individual cells.

Process Data Efficiently with WPS Office

Use WPS Spreadsheet to Handle Complex Lookups

WPS Spreadsheet fully supports both INDEX/MATCH combinations and modern array functions like FILTER, allowing you to retrieve complex multi-column data with ease while ensuring complete format preservation.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your .xlsx workbook containing the ID lists and data tables.
2. Insert the lookup function: Select your target cell, click the 'Formulas' tab on the top ribbon, and choose 'Insert Function'.
3. Configure the formula parameters: Search for INDEX or FILTER in the dialog box, then follow the intuitive prompts to input your array, row number, and column matching criteria.
4. Apply and drag: Press Enter to execute the formula, and use the intelligent fill handle to quickly drag and apply the results across your entire dataset.

Fully compatible with Microsoft Excel (.xlsx) formulas and formatsNative support for advanced dynamic array functions like FILTER and XLOOKUPFree and lightweight alternative for heavy data processingIntuitive formula builder with automatic error checking

Frequently Asked Questions

Why does my INDEX and MATCH formula return an #N/A error?

An #N/A error typically means the User ID in Sheet1 was not found in Sheet2. Check for trailing spaces, formatting mismatches (such as numbers stored as text), or verify that your lookup range includes the target ID.

How is INDEX and MATCH better than VLOOKUP for returning multiple columns?

While VLOOKUP requires you to manually change the column index number for every single column you want to pull, INDEX/MATCH can use dynamic column references (like B$2:B$4) that automatically update and shift to the correct column as you drag the formula to the right.

Can I use XLOOKUP to return multiple columns by ID?

Yes, if your software supports XLOOKUP, you can use a formula like =XLOOKUP(C2, Sheet2!A:A, Sheet2!B:P) to return all columns from B through P dynamically without dragging the formula across multiple cells.

Why is my FILTER formula returning a #SPILL! error?

A #SPILL! error occurs when the adjacent cells where the FILTER function needs to output its data are not completely empty. Clear any existing text, formulas, or hidden spaces in those adjacent cells to allow the formula to expand properly.

Excel & Data Problems