How to Change Lookup Ranges by Condition Using INDEX or XLOOKUP in Excel
Question details
The user needs an Excel formula to return unit statistics from different data ranges dynamically, depending on a user-selected condition and a specific row or unit.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Building a dynamic dashboard or report where the source data range shifts based on user-selected criteria, requiring the lookup array to switch among multiple areas.
- Observed behavior
- The user needs to retrieve a unit statistic by dynamically switching the lookup array among multiple non-contiguous areas or by using filtered dynamic arrays.
Ensure your source data is organized consistently in structured ranges or tables. If your condition selectors use text labels rather than numeric indexes, you will need to familiarize yourself with the MATCH or XMATCH functions to convert them into row or area numbers.
Use the INDEX Function with Multiple Areas
The INDEX function can accept multiple non-contiguous ranges (areas) and use a variable area number to switch between them, making it ideal for changing lookup ranges by a numeric condition.
When you pass multiple ranges enclosed in parentheses to the first argument of the INDEX function, it treats them as separate areas. You can then specify the row number, column number, and finally the area number to determine which range to extract data from.
Group your conditional lookup ranges inside parentheses for the first argument. For example: `(F6:H12, I6:K12, L6:N12)`.
Use cell references for the row number (e.g., `D16`) and the area number (e.g., `C16`) to make your formula dynamic.
Enter the formula `=INDEX((F6:H12,I6:K12,L6:N12), D16, 0, C16)`. Setting the column number to 0 returns the entire row for that specific area.
If you need to add a base value from another range, append another INDEX formula to it: `=INDEX((F6:H12,I6:K12,L6:N12), D16, 0, C16) + INDEX(C6:E12, D16, 0)`.
Use Nested FILTER and Dynamic Array Functions
For complex scenarios with merged headers or highly dynamic columns, nesting FILTER functions with helper arrays provides a flexible and modern lookup method.
Easily Manage Dynamic Lookups in WPS Office
WPS Spreadsheet fully supports advanced lookup formulas, including INDEX, MATCH, XLOOKUP, and dynamic arrays. You can seamlessly create conditional lookup ranges and analyze complex datasets with excellent performance and a familiar interface.
- 1. Open your dataset: Launch WPS Spreadsheet and open your workbook containing the lookup tables and data ranges.
- 2. Enter the INDEX formula: Select your target cell and input the `INDEX` function using multiple areas, just as you would in Microsoft Excel.
- 3. Automate with Drop-downs: Go to the Data tab on the ribbon, select Data Validation, and create drop-down lists for your condition and unit selectors to control the formula dynamically.
- 4. Calculate and review: Press Enter to execute the formula. Change the drop-down values to verify that the lookup range updates instantly and accurately.
Frequently Asked Questions
Can I use XLOOKUP to return values from different ranges conditionally?
Yes. You can use nested IF or IFS functions inside the XLOOKUP return_array argument. For example: `=XLOOKUP(A2, lookup_array, IF(condition=1, return_range1, return_range2))`. This setup allows the function to switch between different output ranges dynamically based on the active condition.
Why is my INDEX formula returning a #REF! error when using multiple areas?
This error typically occurs if the area number specified in your formula is greater than the number of ranges provided in the first argument. Double-check that your area number variable matches the exact count of ranges enclosed in the parentheses.
How do I use MATCH to find the area number for the INDEX function?
You can create a small reference table that lists your condition names next to integer values (1, 2, 3). Then, use `=MATCH(condition_cell, reference_names_range, 0)` to look up the text and return the corresponding integer. Plug this MATCH formula directly into the area_num argument of your INDEX function.
Does WPS Spreadsheet support dynamic array functions like FILTER?
Yes, the latest versions of WPS Office fully support dynamic array formulas, including FILTER, SORT, and UNIQUE. They behave identically to their Excel counterparts, ensuring seamless compatibility and effortless file migration.
