How to Look Up Prices from Another Excel File Using Two Matching Columns
Question details
The user wants to retrieve prices from a large, unsorted external Excel workbook based on two matching criteria, ensuring the result is blank if no match is found.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Looking up and matching pricing data from an external, unsorted file using two specific columns as conditions.
- Observed behavior
- Retrieve the correct price when both conditions are met, or return a blank cell when no item or price matches.
Ensure both the destination file and the external source file containing the pricing data are open before applying the formula to prevent reference errors, and verify that your spreadsheet software version supports the XLOOKUP function.
Use XLOOKUP with Combined Criteria
The best approach for retrieving data based on two or more columns is concatenating the lookup values within an XLOOKUP formula.
XLOOKUP allows you to search for data using multiple conditions by combining arrays. By using the ampersand (&) operator with a delimiter like the pipe character (|), you can create a unique composite key to ensure highly accurate matches without altering your original data structure.
Open both your current working file and the external source file (e.g., File2.xlsx) in your spreadsheet software.
Click on the cell where you want the retrieved price to appear.
Type the formula =IFERROR(XLOOKUP(A2&"|"&B2, [File2.xlsx]Sheet1!$A$2:$A$100000&"|"&[File2.xlsx]Sheet1!$B$2:$B$100000, [File2.xlsx]Sheet1!$C$2:$C$100000, ""), ""). Adjust the cell references to match your specific item, second matching field, and price columns.
Press Enter to calculate the result. Then, double-click or drag the fill handle in the bottom-right corner of the cell to apply this formula down to the rest of the rows.
Merge Tables with Power Query
For exceptionally large, unsorted datasets, Power Query handles data matching much faster than complex array formulas.
Look up Prices Seamlessly with WPS Spreadsheet
WPS Spreadsheet fully supports advanced functions like XLOOKUP, making cross-workbook data retrieval fast and reliable. It handles large datasets effortlessly and offers comprehensive data processing capabilities.
- 1. Download and Install: Get WPS Office for free from the official website and install it on your computer.
- 2. Open Your Workbooks: Open both your target destination file and the pricing source file in WPS Spreadsheet.
- 3. Apply XLOOKUP: Use the XLOOKUP formula with concatenated columns just as you would in Excel to instantly retrieve your prices across files.
Frequently Asked Questions
Why is my XLOOKUP formula returning a #VALUE error?
This typically occurs if the lookup arrays (the columns being searched in the external file) are not of the exact same length. Ensure your row references match perfectly (e.g., $A$2:$A$100000 and $B$2:$B$100000).
Can I use INDEX and MATCH instead of XLOOKUP for two columns?
Yes. You can use an array formula like =INDEX(PriceRange, MATCH(1, (ItemRange=ItemCell)*(Condition2Range=Condition2Cell), 0)). Note that in older spreadsheet versions, you must press Ctrl+Shift+Enter to correctly evaluate this array formula.
Why is a pipe symbol (|) added in the lookup criteria?
The pipe symbol acts as a delimiter to prevent false positive matches. Without it, concatenating 'App' and 'le' yields the same string as concatenating 'Apple' and a blank cell. The delimiter ensures 'App|le' and 'Apple|' are treated as distinct lookups.
