Function Problems

How to Use Excel Formulas to Return Inspection Quantity Based on Lot Size

Maira Mehtab Sep 20, 2026 869 views

Question details

The user needs an Excel formula to look up an inspection quantity from a secondary table based on an inspection plan and a specific lot size, alongside a condition to return the entire lot size for a 'Total' inspection plan.

Product: Excel
Device & OS: not provided
Scenario: Determining precise sampling quantities for quality control or manufacturing inspections based on varying lot sizes and predefined plans.
Observed behavior: Requires a dynamic formula to retrieve the correct inspection quantity from a lookup table, while overriding the lookup to output the full lot size when the plan is set to 'Total'.

Before you start

Ensure that your reference table containing the lot size tiers and corresponding inspection quantities is formatted correctly and sorted from smallest to largest. Familiarize yourself with the cell ranges to properly reference them in your lookup formulas.

Solution 1Recommended

Use INDEX and XMATCH to Retrieve Inspection Quantity

This method looks up the exact sampling quantity by matching the lot size tier and the specific inspection plan in your reference table.

The XMATCH function is highly effective for this scenario because it allows you to find an exact match or the next smaller/larger item, which is perfect for tiered lot sizes.

Select the target cell

Click on the cell where you want the calculated inspection quantity to appear.

Enter the base formula

Type the formula =INDEX(return_array, XMATCH(lookup_value, lookup_array, match_mode)) substituting the ranges with those in your lookup table.

Set the match mode

Configure the XMATCH match_mode to -1 (exact match or next smaller item) or 1 (exact match or next larger item) depending on how your quality control tiers are defined.

Apply the formula

Press Enter to execute the formula, then drag the fill handle down to apply it to the remaining rows.

XMATCH Compatibility: XMATCH is available in newer versions of Excel and WPS Spreadsheet. If you are using an older version, you may need to use the traditional MATCH function with a match type of 1 or -1.

Solution 2

Incorporate an IF Function for a 'Total' Inspection Plan

Use this approach when you need to override the standard lookup and return the entire lot size if the inspection plan is specified as 'Total'.

Powerful Spreadsheet Tool

Calculate Complex Quality Control Formulas Easily with WPS Office

WPS Spreadsheet fully supports advanced lookup functions like INDEX, MATCH, XMATCH, and dynamic arrays, making it incredibly simple to build dynamic inspection tables.

1. Open your data: Launch WPS Spreadsheet and open the file containing your lot sizes and lookup tables.
2. Enter the formula: Click on the target cell and enter the IF and INDEX/XMATCH formula as instructed.
3. Calculate seamlessly: Press Enter to calculate the exact inspection quantity, leveraging seamless calculation speed.

Fully compatible with Microsoft Excel (.xlsx) formats and all standard formulas.Supports advanced dynamic arrays and XMATCH for complex lookups.Lightweight, fast, and runs smoothly on Windows, Mac, Linux, iOS, and Android.Clean and familiar user interface that requires no learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use VLOOKUP instead of INDEX and XMATCH for tiered lot sizes?

Yes, you can use VLOOKUP with an approximate match by setting the last argument to TRUE (or 1). However, INDEX and XMATCH offer more flexibility, especially if you need to perform a two-way lookup (matching both rows and columns).

Why is my XMATCH formula returning an #N/A error?

An #N/A error typically means the lookup value does not exist in the lookup array and no appropriate fallback was found. Ensure your match_mode parameter is correctly set (e.g., -1 for next smaller or 1 for next larger) if the exact lot size isn't explicitly listed in your reference table.

How do I handle multiple different inspection plans in a single reference table?

You can use an INDEX function paired with two XMATCH functions—one to find the correct row (based on lot size) and another to find the correct column (based on the specific inspection plan name like Normal, Tightened, or Reduced).

Excel & Data Problems