Function Problems

How to Use Excel XLOOKUP to Calculate Cases from Product Quantities

Maira Mehtab Sep 22, 2026 868 views

Question details

The user wants to find out how to use the XLOOKUP function, combined with an IFERROR wrapper, to look up a product's units per case and calculate the total number of cases needed based on an order quantity.

Product: Excel
Device & OS: not provided
Scenario: Calculating the total number of cases required for an order by referencing a separate product list that contains the number of units per case.
Observed behavior: To output the correct number of cases automatically and ensure that if a product ID is missing or incorrect, the cell remains blank without showing formula errors.

Before you start

Verify that your master product list containing Product IDs and units per case is properly organized, and ensure you are using a spreadsheet software version that supports the XLOOKUP function.

Solution 1Recommended

Calculate Cases Using XLOOKUP with an IFERROR Wrapper (Cross-Sheet)

Use this recommended method when your main product list and order quantities are located on different worksheets to avoid clutter and handle missing data gracefully.

By dividing the order quantity by the output of an XLOOKUP function, you can determine how many cases are needed. Wrapping the entire calculation in an IFERROR formula ensures that if a Product ID doesn't exist, the cell simply remains blank instead of showing an #N/A error.

Select the target cell

Navigate to your order sheet and click on the cell where you want the calculated cases to appear (e.g., cell G2).

Enter the XLOOKUP formula

Type the formula `=IFERROR(F2/XLOOKUP(E2,'Product List'!$A$2:$A$100,'Product List'!$B$2:$B$100),"")` into the formula bar. In this example, F2 is the order quantity, E2 is the Product ID, and the ranges refer to your separate 'Product List' sheet.

Apply to the entire column

Press Enter to execute the formula. Then, click the small square at the bottom-right corner of cell G2 and drag it down to fill the rest of the column.

Use Absolute References: Always use the dollar signs ($) in your lookup arrays (like $A$2:$A$100). This locks the reference list so it won't shift when you drag the formula down to other rows.

Solution 2

Calculate Cases Using XLOOKUP on the Same Sheet

A simpler formula approach when both your product reference list and order data reside on the same worksheet.

Advanced Spreadsheet Capabilities

Use WPS Spreadsheet for Flawless XLOOKUP Calculations

WPS Spreadsheet fully supports advanced Excel functions like XLOOKUP and IFERROR out of the box. Manage your inventory lists and calculate complex order quantities effortlessly using a familiar interface.

1. Open your inventory file: Launch WPS Spreadsheet and open your workbook containing the product list and order data.
2. Select the destination cell: Click on the cell where the case calculation result should be displayed.
3. Insert the formula: Type the formula `=IFERROR(F2/XLOOKUP(...))` using WPS's intelligent auto-complete prompt to select your data ranges easily.
4. Fill the series: Drag the fill handle down to calculate the case quantities for all remaining products instantly.

100% compatibility with Microsoft Excel formulas, formatting, and file typesNative support for modern lookup formulas including XLOOKUP and VLOOKUPBuilt-in error checking to help you troubleshoot spreadsheet data easilyLightweight, fast, and absolutely free for your daily data management

microsoft office alternative - wps office

Frequently Asked Questions

Why does my XLOOKUP formula return an #N/A error?

The #N/A error occurs when the XLOOKUP function cannot find the lookup value (such as your Product ID) in the designated lookup array. By wrapping your formula in an `=IFERROR(..., "")` function, you can force the cell to remain blank or display a custom message instead of showing the error.

Can I use VLOOKUP instead of XLOOKUP for this calculation?

Yes, you can substitute XLOOKUP with VLOOKUP by using `=F2/VLOOKUP(E2, $A$2:$B$100, 2, FALSE)`. However, XLOOKUP is often preferred because it is faster, more flexible, and does not require the return column to be strictly to the right of your lookup column.

Why is it important to use dollar signs ($) in the lookup ranges?

The dollar signs ($) create an absolute reference (e.g., $A$2:$A$100). This means the specific cell range remains completely fixed. If you omit them, the lookup range will shift down as you copy the formula to lower rows, causing the function to miss data and produce incorrect results.

Excel & Data Problems