How to Return the Maximum Matching Value from Multiple Excel Ranges
Question details
The user needs a method to look up a specific value across two separate data ranges, return the highest corresponding value from another column, and display a custom text message instead of an error if the value is not found anywhere.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Looking up data across non-contiguous or separate tables to find and extract the highest corresponding numerical value for a specific identifier.
- Observed behavior
- Standard lookups return the first match or fail with #N/A errors when checking multiple ranges, making it difficult to find the true maximum value without combining advanced mathematical and lookup functions.
Ensure your lookup values are spelled identically across your data tables and verify that the return columns contain numeric values so the MAX function can evaluate them properly.
Use XLOOKUP combined with MAX and IF functions
Combine the XLOOKUP function to pull data from separate ranges, MAX to find the highest value, and IF with COUNTIF to handle missing data gracefully.
To evaluate values from multiple separate ranges, you can nest multiple XLOOKUP functions inside a MAX function. By utilizing the built-in fallback argument in XLOOKUP, you can prevent standard #N/A errors from propagating to the MAX calculation.
Determine your lookup value cell (e.g., F6), your two lookup ranges (e.g., D15:D50 and D56:D91), and your two return ranges (e.g., K15:K50 and K56:K91).
Write the first lookup formula with a fallback value of 0: XLOOKUP(F6,D15:D50,K15:K50,0). Then write the second one: XLOOKUP(F6,D56:D91,K56:K91,0).
Wrap both of these XLOOKUP functions inside a MAX function to evaluate which table holds the higher value: MAX(XLOOKUP(F6,D15:D50,K15:K50,0), XLOOKUP(F6,D56:D91,K56:K91,0)).
Wrap the entire formula in an IF statement using COUNTIF to return a custom text string when the value doesn't exist in either range: =IF(COUNTIF(D15:D50,F6)+COUNTIF(D56:D91,F6)=0,"Not found",MAX(XLOOKUP(F6,D15:D50,K15:K50,0),XLOOKUP(F6,D56:D91,K56:K91,0))).
Easily perform complex lookups with WPS Spreadsheet
WPS Spreadsheet fully supports advanced modern formulas like XLOOKUP, IF, and MAX, making it simple to retrieve, compare, and analyze data across multiple tables seamlessly.
- 1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your spreadsheet. Select the cell where you want to display the maximum matched result.
- 2. Use the Function Wizard: Navigate to the Formulas tab on the ribbon and click 'Insert Function' to easily search for XLOOKUP or MAX.
- 3. Input the nested formula: Paste or type your combined =IF(COUNTIF(...), MAX(XLOOKUP(...))) formula directly into the formula bar.
- 4. Execute the calculation: Press Enter to instantly generate accurate results comparing values across multiple data ranges.
Frequently Asked Questions
Can I use VLOOKUP instead of XLOOKUP for this multiple range comparison?
While VLOOKUP can technically be used, it requires wrapping each lookup in an IFERROR function to handle missing data. XLOOKUP is highly recommended because it features a built-in fallback argument (the fourth parameter) that prevents errors much more cleanly.
What if my return columns contain negative numbers?
If your return values include negative numbers, setting the XLOOKUP fallback to 0 will cause the MAX function to return 0 instead of a legitimate negative value. In such cases, replace the 0 fallback with a number significantly lower than your expected data range, such as -999999.
Why is my MAX function returning an #N/A error?
This happens if one of the XLOOKUP ranges does not find a match and returns an #N/A error, which propagates through the MAX formula. Ensure the fourth parameter (if_not_found) in your XLOOKUP formula is explicitly set to a numeric value like 0 to prevent this.
