'%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 Look Up a Value from Two Possible Columns in Excel
Question details
The user needs a formula to look up a corresponding value from a mapping worksheet when the search key could be located in either of two possible columns.
- Product
- Excel and WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Data mapping and lookup where the exact column containing the lookup value is uncertain.
- Observed behavior
- Standard lookup functions only search one specific column, returning an error if the value is located in the alternative column.
Verify that your mapping worksheet contains clear, distinct columns for the lookup keys and return values, and ensure your spreadsheet software supports the XLOOKUP function.
Use a Combined IFERROR and XLOOKUP Formula
This approach attempts to find the value in the first column, and if it results in an error, automatically falls back to searching the second column.
By nesting an XLOOKUP function inside an IFERROR function, you can instruct the spreadsheet to check a primary column first. If the lookup value is not found there, IFERROR triggers the second XLOOKUP to check the secondary column.
Click on the cell where you want the mapped region or corresponding value to appear.
Type the formula: =IFERROR(XLOOKUP(F2,Map!A:A,Map!B:B), XLOOKUP(G2,Map!A:A,Map!B:B,"")). Adjust 'F2' and 'G2' to match your possible input cells, and 'Map!A:A' and 'Map!B:B' to match your actual mapping worksheet and column ranges.
Press Enter to execute the formula. Then, click and drag the fill handle at the bottom right corner of the cell down to apply it to the rest of the column.
Effortlessly Manage Complex Lookups with WPS Spreadsheet
WPS Spreadsheet provides robust, built-in support for advanced functions like XLOOKUP and IFERROR, allowing you to seamlessly map and look up data across multiple columns without compatibility issues.
- 1. Open your dataset in WPS: Launch WPS Office and open your .xlsx file containing the data and mapping worksheets.
- 2. Insert the primary lookup: Select your output cell and start typing the IFERROR and XLOOKUP formula to target your first potential column.
- 3. Add the secondary lookup: Complete the formula by inserting the second XLOOKUP as the fallback condition within the IFERROR function.
- 4. Populate the column: Press Enter, then double-click the fill handle to automatically map the values for all remaining rows.
Frequently Asked Questions
Can I look up a value across more than two columns?
Yes, you can nest multiple IFERROR functions to check three or more columns. For example: =IFERROR(XLOOKUP(F2,...), IFERROR(XLOOKUP(G2,...), XLOOKUP(H2,...))).
Why does my formula return a #REF! error?
A #REF! error typically occurs if the worksheet referenced in the formula (such as 'Map!') has been deleted or renamed, or if the column ranges are invalid. Ensure the sheet name in your formula exactly matches the actual tab name at the bottom of your workbook.
What does the "" at the end of the XLOOKUP formula do?
The empty double quotes "" serve as the 'if_not_found' argument for the second XLOOKUP. If the value is found in neither the first nor the second column, the formula will return a blank cell instead of an ugly #N/A error.
