WPS Spreadsheet: Use INDEX with Selected Nonadjacent Cells
Question details
The user needs to use INDEX and MATCH to return a label associated with the largest value among selected nonadjacent cells.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A row contains alternating or otherwise nonadjacent labels and values, and only specific value cells should be included in the lookup.
- Observed behavior
- INDEX and MATCH work naturally with contiguous ranges, but the required lookup set contains nonadjacent cells that must be treated as a custom array.
Confirm which columns belong to the lookup set and ensure the selected value cells contain numbers. Check whether your WPS Spreadsheet version supports LET, CHOOSECOLS, and XLOOKUP before choosing the formula.
Use dynamic array functions for selected nonadjacent cells
Use this method when your WPS Spreadsheet version supports LET, CHOOSECOLS, and XLOOKUP; it creates matching label and value arrays from selected columns.
The two CHOOSECOLS functions must use the same column positions so that each value maps to the correct label. Replace the example ranges and column indexes with the ranges in your worksheet.
Click the cell where you want the matching label to appear, then enter a formula beginning with =LET(.
Enter =LET(labels,CHOOSECOLS(F4:T4,1,3,5,7,9,11,13,15),values,CHOOSECOLS(F6:T6,1,3,5,7,9,11,13,15),XLOOKUP(MAX(values),values,labels)) and adjust the ranges or column indexes as needed.
Press Enter in WPS Spreadsheet. The formula returns the label from F4:T4 that corresponds to the largest selected value in F6:T6.
Use INDEX and MATCH with a legacy-compatible formula
Use this option when dynamic array functions are unavailable and the maximum value is known to occur in one of the selected columns.
Build nonadjacent-cell lookups in WPS Spreadsheet
WPS Spreadsheet lets you calculate a maximum across selected cells and return its corresponding label with INDEX and MATCH. Use dynamic array functions when available, or apply the compatible legacy formula for older spreadsheet versions.
- 1. Open the workbook: Launch WPS Office, choose Spreadsheet, and open the workbook containing the label and value rows.
- 2. Identify the selected columns: Review the worksheet and list the nonadjacent columns that should participate in the lookup.
- 3. Enter the appropriate formula: Click the result cell and enter the LET, CHOOSECOLS, and XLOOKUP formula if supported; otherwise enter the INDEX, MATCH, and MAX formula.
- 4. Validate the returned label: Compare the result with the selected value cells and check for duplicate maximum values or an unselected cell with a larger value.
Frequently Asked Questions
Can INDEX and MATCH look up values in nonadjacent cells directly?
They work most reliably with a contiguous lookup array. For nonadjacent cells, first create a selected array with CHOOSECOLS or use MAX with the individual cell references in a legacy-compatible formula.
How do I select alternating columns in WPS Spreadsheet?
Use CHOOSECOLS with the source row and the required column positions, such as CHOOSECOLS(F6:T6,1,3,5,7,9,11,13,15). Use the same positions for the label row.
What happens if two selected cells have the same maximum value?
XLOOKUP returns the first matching label. The legacy MATCH formula also returns the first match, so duplicate maximum values do not produce a unique result unless you add a tie-breaking rule.
Why does the older INDEX and MATCH formula return the wrong label?
The legacy formula searches the full range in MATCH. It can return an incorrect result if an unselected cell contains the same or a larger value, so verify that the maximum occurs in a selected column and that values are unique.
