Compare Two Excel Columns and Return Matching Text | WPS Spreadsheet
Question details
The user needs to compare a search value from one column against a lookup array in a second column, and return the corresponding text from a third column using spreadsheet formulas.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Looking up and extracting corresponding data from a dataset based on a matching criteria across different columns.
- Observed behavior
- Needs an automated formula to extract matched data dynamically without performing manual row-by-row comparisons.
Ensure your data ranges are properly defined and free of hidden leading or trailing spaces, as these formatting inconsistencies are the most common cause of formula matching errors.
Use INDEX and MATCH combined with IFERROR
This is the most versatile and backward-compatible method to look up values across columns, even if the return column is located to the left of the lookup column.
The INDEX and MATCH combination is a powerful alternative to VLOOKUP. MATCH finds the position of your search value, and INDEX uses that position to retrieve the corresponding value from the target column. Wrapping it in IFERROR ensures that empty cells are displayed instead of #N/A errors when a match isn't found.
Assume column C contains the value you want to find, column A is the lookup range to compare against, and column B contains the values to return.
Click on the cell (for example, D1) where you want the returned text to appear.
Type the formula: =IFERROR(INDEX($B$1:$B$50,MATCH(C1,$A$1:$A$50,0)),"") into the formula bar.
Press Enter to execute the formula. Then, click the small square at the bottom right corner of the cell and drag it down to fill the formula for the rest of the column.
Use the Modern XLOOKUP Function
If you are using a modern spreadsheet version, XLOOKUP provides a simpler, more intuitive syntax to achieve the exact same result.
Easily Compare and Analyze Data in WPS Spreadsheet
WPS Office provides a robust Spreadsheet tool equipped with all advanced lookup functions, including INDEX, MATCH, VLOOKUP, and XLOOKUP, allowing you to seamlessly process complex data sets and compare columns with ease.
- 1. Open your workbook: Launch WPS Spreadsheet and open your document containing the data columns you need to compare.
- 2. Select the output cell: Click on the cell where you want the matching text to be returned.
- 3. Insert the lookup function: Go to the Formulas tab to insert a lookup function, or type your INDEX/MATCH or XLOOKUP formula directly into the cell.
- 4. Apply to multiple rows: Press Enter, then drag the fill handle at the bottom-right of the cell to effortlessly copy the formula down your entire dataset.
Frequently Asked Questions
Why is my lookup formula returning #N/A?
This usually happens if the exact value doesn't exist in the lookup array, or if there are invisible formatting differences such as leading or trailing spaces. You can use the TRIM() function on your data to remove unwanted spaces.
Can I use VLOOKUP instead of INDEX and MATCH?
Yes, VLOOKUP can be used if your return column is situated to the right of your lookup column. However, INDEX and MATCH is more flexible because it can look up values to the left and is generally less prone to breaking if you insert new columns.
How do I perform a case-sensitive lookup?
Standard lookup functions like VLOOKUP, MATCH, and XLOOKUP are not case-sensitive. To perform a case-sensitive lookup, you need to use the EXACT function combined with INDEX and MATCH entered as an array formula.
