'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Use Excel Formulas to Return a Matching Row of Data
Question details
The user needs to retrieve and return multiple related values across a row based on a specific lookup reference value in an Excel table.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Looking up data dynamically across rows to auto-populate reports, invoices, or master dashboards.
- Observed behavior
- The goal is to automatically fill cells in a horizontal row with data corresponding to a matched lookup value from a source table.
Ensure your source data has a unique column for the lookup value to prevent incorrect or duplicate matches, and check that both data ranges are formatted similarly.
Use INDEX and MATCH Formulas (Compatible with All Excel Versions)
The combination of INDEX and MATCH is a robust, universally compatible way to look up a value and return an entire row of data, working perfectly in older versions like Excel 2019.
By nesting a MATCH function inside an INDEX function, you can search for a specific value in a column and return the corresponding value from another column. Locking the column reference ensures you can drag the formula horizontally across the row.
Click on the cell where you want the first returned value to appear (for example, B5).
Type the formula: =IFERROR(INDEX(B$8:B$9999,MATCH($C5,$C$8:$C$9999,0)),"") where $C5 is your lookup value and $C$8:$C$9999 is the lookup column.
Press Enter. Select the cell, click the small square in the bottom-right corner (fill handle), and drag it across the row to populate the remaining columns.
Reference Data on Another Worksheet
If your master table is on a different tab, you must include the sheet name in your formula to direct Excel to the correct data range.
Use the XLOOKUP Function (Excel 365 and 2021)
XLOOKUP is a newer, simpler formula that can easily 'spill' an entire row of matching data without the need to drag the formula across columns.
Use WPS Spreadsheet to Easily Look Up and Return Row Data
WPS Office offers a powerful Spreadsheet application that fully supports advanced lookup functions like XLOOKUP, INDEX, and MATCH. You can manage complex datasets and cross-sheet references smoothly without paying for expensive software.
- 1. Open Your Workbook: Launch WPS Spreadsheet and open the file containing your lookup table and destination table.
- 2. Select the Target Cell: Click on the cell where you want to output the lookup result.
- 3. Input the Formula: Go to the Formulas tab or type your =XLOOKUP or =INDEX(MATCH()) formula directly into the formula bar.
- 4. Drag to Fill: Press Enter, then use the fill handle in the corner of the cell to copy the formula across the row if you are using INDEX and MATCH.
Frequently Asked Questions
Why does my INDEX MATCH formula return #N/A?
This error occurs when the exact lookup value cannot be found in the reference array. Ensure there are no leading or trailing spaces, check that numbers aren't stored as text, and confirm the lookup array covers the correct range.
Can I look up multiple criteria at once?
Yes, you can use an array formula with INDEX and MATCH by concatenating lookup values and arrays using the ampersand (&) symbol. Similarly, XLOOKUP allows multiple criteria by linking them in the lookup arguments.
How do I return a row of data horizontally from a vertical list?
You can combine your lookup formula with the TRANSPOSE function to flip the orientation of the returned data. Alternatively, you can use horizontal lookup functions like HLOOKUP depending on how your source data is structured.
Why is XLOOKUP returning a #NAME? error in my spreadsheet?
XLOOKUP is only available in newer versions (Excel 365, Excel 2021, and modern versions of WPS Office). If you see the #NAME? error, your version does not support XLOOKUP, and you must use the INDEX and MATCH solution instead.
