Function Problems

How to Use XLOOKUP to Return Rates from Multiple Excel Tables

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to retrieve specific rates for a quote sheet based on three variables: division, customer, and line item, where the rates are currently stored across multiple division-specific tables.

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Building a quote sheet that requires dynamically selecting a division, customer, and line item to fetch the correct matching rate.
Observed behavior: Rates are split across multiple tables by division, making standard single-criterion lookups insufficient to retrieve the correct data.

Before you start

Ensure you have a clear understanding of your table structures and that the customer names and line item headers match exactly across all your division tables before building complex formulas.

Solution 1Recommended

Combine Tables and Use Multiple-Criteria XLOOKUP

Normalizing your data into a single master table is the most scalable approach for multi-criteria lookups.

Instead of managing multiple grid tables, combining your data into one structured table with distinct columns (Division, Customer, Item, Rate) drastically simplifies the lookup process and reduces formula complexity.

Consolidate your tables

Create a new master table sheet with columns for Division, Customer, Item, and Rate. Copy and paste all the data from your separate division tables into this single, continuous list.

Set up the XLOOKUP formula

In your quote sheet, use the formula =XLOOKUP(1, (DivisionRange=SelectedDivision)*(CustomerRange=SelectedCustomer)*(ItemRange=SelectedItem), RateRange) to match multiple criteria simultaneously.

Apply and format

Press Enter to retrieve the matching rate. Ensure all lookup ranges are set as absolute references (using the F4 key) if you plan to copy this formula to other cells.

Best Practice: Normalizing your data into a single table makes future updates much easier and helps prevent lookup errors compared to managing nested logic across multiple sheets.

Solution 2

Perform a Two-Way Lookup Across Separate Tables

If you cannot consolidate the tables, use nested lookup functions to choose the table dynamically based on the division.

Advanced Spreadsheet Functions

Master Complex Lookups with WPS Spreadsheet

WPS Spreadsheet fully supports advanced dynamic array functions like XLOOKUP, making it incredibly easy to handle complex, multi-criteria data retrieval tasks across multiple tables.

1. Open your workbook: Launch WPS Spreadsheet and open your existing file containing the multiple rate tables and the quote sheet.
2. Select the target cell: Click on the specific cell in your quote sheet where you want the calculated rate to appear.
3. Enter the XLOOKUP formula: Type =XLOOKUP( into the formula bar and use the visual formula hints provided by WPS to accurately select your lookup criteria ranges and return arrays.
4. Execute the lookup: Press Enter to apply the formula and instantly retrieve the correct division rate.

Fully compatible with Microsoft Excel formulas, including XLOOKUP, XMATCH, and INDEX.Handle large datasets with multiple tables efficiently without performance lag.Intuitive formula builder to assist with writing and troubleshooting complex nested lookups.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use VLOOKUP instead of XLOOKUP for multi-criteria lookup?

While VLOOKUP can be used by creating temporary helper columns that concatenate your criteria, XLOOKUP is vastly superior. XLOOKUP can perform multi-criteria searches natively using Boolean logic arrays without needing to alter your dataset.

Why is my multi-criteria XLOOKUP returning a #VALUE error?

A #VALUE error usually occurs if the ranges used in your multiple criteria logic (e.g., Range1=Criteria1) are not exactly the same size. Ensure all lookup arrays you are multiplying together have the exact same number of rows and columns.

What does multiplying criteria with an asterisk (*) do in XLOOKUP?

In multi-criteria formulas like =XLOOKUP(1, (Range1=Crit1)*(Range2=Crit2), ReturnRange), the asterisk acts as an AND operator. It multiplies the TRUE (1) and FALSE (0) arrays, resulting in a 1 only where all conditions are met simultaneously.

Excel & Data Problems