Google Sheets: Compare Headers and Values Across Two Ranges with XLOOKUP
Question details
The user needs to match a subject from a header with a subject list, compare the corresponding row values against threshold headers, and return the appropriate threshold result.
- Product
- Google Sheets
- Device & OS
- not provided
- Scenario
- A worksheet contains subjects in M8:M14, comparison values in N8:Q14, and threshold headers in N7:Q7. The formula must use the subject and value headers in A6 and A7 to identify the result.
- Observed behavior
- The required result depends on matching the subject row first and then finding the appropriate threshold column for the selected value.
Confirm that the subject list in M8:M14 aligns with the comparison values in N8:Q14, and that the threshold headers in N7:Q7 are arranged for the selected approximate-match logic. Extend the ranges if your source table contains more rows.
Use a nested XLOOKUP to match the subject and threshold
Use this formula when the subject determines the comparison row and the value must be matched against threshold headers.
The inner XLOOKUP finds the subject from M8:M14 and returns its corresponding values across N8:Q14. The outer XLOOKUP compares the value in A7 with that returned row and returns the matching threshold from N7:Q7.
The -1 match mode returns an exact match or the next smaller value. Keep the threshold values in the order required by your matching logic.
In Google Sheets, click the cell where you want the threshold result to appear.
Enter =XLOOKUP(A$7,XLOOKUP(A$6,$M$8:$M$14,$N$8:$Q$14),$N$7:$Q$7,, -1) and press Enter.
Verify that M8:M14 contains the full subject list and N8:Q14 contains the corresponding values. Adjust both ranges if additional source rows exist.
Check the values in N7:Q7 and reorder them if needed so the -1 match mode returns the intended threshold.
Compare Subject Values and Thresholds in WPS Spreadsheet
WPS Spreadsheet supports nested XLOOKUP formulas for matching a subject row, comparing its values with threshold headers, and returning the appropriate result. Open or recreate the worksheet in WPS Spreadsheet and use the same formula structure.
- 1. Open the spreadsheet in WPS Spreadsheet: Launch WPS Office, choose Spreadsheet, and open the workbook containing the subject and threshold ranges.
- 2. Select the output cell: Click the cell where the comparison result should appear.
- 3. Enter the nested XLOOKUP: Type =XLOOKUP(A$7,XLOOKUP(A$6,$M$8:$M$14,$N$8:$Q$14),$N$7:$Q$7,, -1) and press Enter.
- 4. Validate the ranges and result: Confirm that the subject and value ranges cover all source rows and that the returned threshold matches the intended ordering.
Frequently Asked Questions
What does the inner XLOOKUP do in this formula?
The inner XLOOKUP uses A6 to find a subject in M8:M14 and returns the matching row of values from N8:Q14.
What does the outer XLOOKUP return?
The outer XLOOKUP compares A7 with the values returned by the inner lookup and returns the corresponding threshold from N7:Q7.
Why is the XLOOKUP match mode set to -1?
The -1 match mode finds an exact match or the next smaller value. Use a different match mode if your comparison requires an exact match or the next larger value.
Can I include more rows in the subject table?
Yes. Expand both the subject range and the comparison range together, such as changing M8:M14 to M8:M20 and N8:Q14 to N8:Q20.
Why might the formula return an incorrect threshold?
Check that A6 exactly matches a subject in M8:M14, that each subject row aligns with N8:Q14, and that the threshold values in N7:Q7 are ordered correctly for the selected match mode.
