Use One Excel Cell to Return Matching Values From Another Row
Question details
Use a value selected in one Excel cell to return the corresponding value from another row or range.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet contains related values in separate rows or columns, and the user wants a formula that displays the matching result after selecting or entering a lookup value.
- Observed behavior
- The required formula depends on whether the selected value identifies a column, a row, or an intersection of both.
Identify the cell that contains the selected lookup value and the exact row or column that contains the result to return. Ensure lookup labels are consistent, with no unintended spaces or duplicate labels unless duplicates are expected.
Use XLOOKUP to return a value from a matching Excel column
Use this method when the selected value appears in a header row or lookup range and you need the related value from another row.
XLOOKUP finds the selected value in one range and returns the value from the same position in a second range. For example, if B1 contains a selected month and row 2 contains month names, the formula can return the matching sales value from row 3.
Click the cell where you want the related value to appear, such as B5.
Type a formula such as =XLOOKUP(B1,$B$2:$M$2,$B$3:$M$3,"Not found"). Replace B1 with the selected-value cell, B2:M2 with the lookup row, and B3:M3 with the row that contains the values to return.
Press Enter, then change the value in B1 or choose a different item from its drop-down list. The result cell updates with the matching value from the return row.
Use INDEX and MATCH for older Excel versions or two-way lookups
Use this method when XLOOKUP is unavailable or when one selected cell identifies a row and another identifies a column.
Return Related Row Values With Lookup Formulas in WPS Spreadsheets
WPS Spreadsheets lets you use XLOOKUP, INDEX, and MATCH to return a related value when a user selects an item in another cell. Build a simple lookup table, reference the selection cell, and use a formula to update the result automatically.
- 1. Open the worksheet in WPS Spreadsheets: Open your .xlsx or .xls file in WPS Office and select the sheet containing the lookup table.
- 2. Set up the selection cell: Click the input cell, then choose Data > Data Validation to create a list if users need to select from predefined lookup labels.
- 3. Add the lookup formula: Click the result cell and enter =XLOOKUP(B1,B2:M2,B3:M3,"Not found") or an INDEX and MATCH formula that matches your table layout.
- 4. Test each selected value: Change the value in the selection cell and confirm that the formula returns the corresponding value from the related row.
Frequently Asked Questions
How do I return a value from another row based on a selected cell in Excel?
Use XLOOKUP when the selected value is in a matching header or lookup range. For example, =XLOOKUP(B1,B2:M2,B3:M3) finds B1 in B2:M2 and returns the value from the same position in B3:M3.
What formula should I use if my Excel version does not include XLOOKUP?
Use INDEX and MATCH: =INDEX(B3:M3,MATCH(B1,B2:M2,0)). MATCH locates the selected value, and INDEX returns the related value from the return row.
Can one selected value return a result from a table with row and column headings?
Yes. Use a two-way INDEX and MATCH formula, such as =INDEX(B2:M10,MATCH(B1,A2:A10,0),MATCH(C1,B1:M1,0)), where B1 selects the row and C1 selects the column.
Why does my lookup formula return #N/A?
The selected value is not an exact match in the lookup range, or it contains extra spaces or different text formatting. Check the lookup cell and labels, and use the exact-match argument 0 in MATCH.
