How to Return a Corresponding Value in Excel (First or All Matches)
Question details
The user needs to search for a specific number in a column range and retrieve the corresponding value from a different column, handling either the first match or multiple matches.
- Product
- Spreadsheets
- Device & OS
- not provided
- Scenario
- Looking up data across columns to retrieve matching values based on a specific search criterion.
- Observed behavior
- The user wants formula solutions capable of extracting the first matching result or returning all corresponding results as a dynamic array or concatenated string.
Ensure that your lookup column and your return column are exactly the same height (e.g., rows 14 to 60) to prevent formula range mismatch errors.
Use XLOOKUP to Return the First Matching Value
The XLOOKUP function is the most efficient and straightforward way to find the very first matching value in a range.
XLOOKUP searches a specified range for a search key and returns the corresponding item from a second range. It replaces the older VLOOKUP function and does not require the return column to be on the right side.
Click on the cell where you want the corresponding value to appear.
Type the formula =XLOOKUP(B8, B14:B60, F14:F60, "No corresponding value found") and press Enter.
The spreadsheet will search for the value from B8 within B14:B60. Once it finds the first match, it will return the value from the exact same row in column F.
Use the FILTER Function to Return All Matches as a Spill Range
If there are multiple matching values and you want to extract all of them into separate cells, use the FILTER function.
Combine TEXTJOIN and FILTER for All Matches in One Cell
When you need all matching values grouped together in a single cell separated by commas or semicolons.
Solve Complex Data Lookups Easily with WPS Spreadsheet
WPS Spreadsheet fully supports advanced array functions like XLOOKUP, FILTER, and TEXTJOIN. It provides a familiar, fast, and free environment to analyze and manage your data without compatibility issues.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your .xlsx data file.
- 2. Apply Lookup Formulas: Click on any empty cell and type your preferred lookup formula, such as =XLOOKUP() or =FILTER().
- 3. Calculate Results: Press Enter to instantly retrieve your matching data.
Frequently Asked Questions
Why does my XLOOKUP formula return an error?
Errors typically occur if the value you are searching for does not exist in the lookup array or if the lookup ranges are not identical in size. Ensure B14:B60 and F14:F60 contain the exact same number of rows.
Can I use VLOOKUP instead of XLOOKUP to return the corresponding value?
Yes, but VLOOKUP requires the return column to be strictly to the right of the lookup column. If column F is to the right of column B, a formula like =VLOOKUP(B8, B14:F60, 5, FALSE) will successfully find the first match.
Why is my FILTER function returning a #SPILL! error?
A #SPILL! error indicates the FILTER function found multiple matches, but there is existing data blocking the adjacent cells below it. Clear the cells below your formula so the results have enough room to expand.
How do I return multiple matches horizontally instead of vertically?
You can wrap your FILTER function in a TRANSPOSE function, such as =TRANSPOSE(FILTER(F14:F60, B14:B60=B8)), to make the results spill across adjacent columns instead of down rows.
