Inventory Allocation Without Double-Counting

How to assign stock items to orders one-to-one in Excel — no VBA, no helper columns, no duplicates

In this article: The Real-World Problem • Why Traditional Lookups Fail • The xmatchexclusive Approach • Worked Example • Multi-Column Criteria • Edge Cases

The Real-World Problem

A warehouse manager receives a batch of customer orders. Each order needs one specific product variant from stock. The stock list has 200 rows — some variants appear multiple times because multiple units are available.

The goal: for each order, return one stock item of the right variant and mark it as assigned. No two orders should share the same physical stock line.

This comes up constantly in:

• E-commerce fulfilment: matching individual serialised units to orders
• Spare-parts allocation: assigning specific components to work orders
• Loan equipment: one device per borrower from a shared pool
• Sample distribution: giving each research participant a unique sample

Why this is harder than it looks When you have 5 units of “Widget A” in stock and 5 orders for “Widget A”, you want orders to get rows 1, 2, 3, 4, 5 of the stock table respectively. VLOOKUP gives all 5 orders row 1.

Why VLOOKUP + COUNTIF Falls Apart

The standard community workaround concatenates a COUNTIF to the lookup value to create artificial uniqueness

=VLOOKUP(A2&COUNTIF($A$2:A2,A2), HelperCol, 3, FALSE)

This creates lookup keys like “Widget-A1”, “Widget-A2”, “Widget-A3”. It works — until:

• The stock table is re-sorted (the helper column key numbering breaks)
• A new product variant is added mid-table (all keys below it shift)
• Two users edit the workbook simultaneously (count collisions)
• You need to match on more than one column (e.g., variant AND size)
• The formula also silently fails rather than returning a clear “out of stock” signal — it just returns #N/A without context.

The xmatchexclusive Approach

XMatchExclusive treats the stock range as a reservation pool. Each row can only be matched once across the entire workbook, regardless of how many cells call the function.

Basic single-column allocation:

=XME.XMATCHEXCLUSIVE(B2, $F$2:$F$200, $G$2:$G$200, "OUT OF STOCK")

Where B2 is the product variant on the order, $F$2:$F$200 is the variant column in the stock table, and $G$2:$G$200 is the stock line ID to return.

Get mohammed’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Drag this down the orders table. Each order gets a unique stock line. When all units of a variant are exhausted, the function returns “OUT OF STOCK” — a clean, human-readable signal

Worked Example: Laptop Allocation

Scenario: IT department has 8 laptops to assign to new starters. Each laptop has a unique asset tag. New starters are listed in an onboarding sheet.

Stock Table (columns F:G):

Onboarding Table (columns A:C):

Results

Result: Emp 001 gets ASSET-0041, Emp 002 gets ASSET-0042 (not a repeat of 0041), Emp 003 gets ASSET-0044. Each asset assigned once. No duplicates, can still use xlookup on Stock Table to identify allocated assets.

Multi-Column Criteria: Matching on Variant AND Size

XMatchExclusive supports multi-column lookup — specify multiple lookup columns and the function matches only rows where all criteria match simultaneously.

Example: Stock has both product variant and size. Orders specify both. You want to match on both:

=XME.XMATCHEXCLUSIVE(B2:C2, $F$2:$G$200, $H$2:$H$200, "OUT OF STOCK")

Where B2:C2 contains the variant and size for this order (e.g., “Widget A”, “Large”), and $F$2:$G$200 is the two-column lookup range in the stock table.

This eliminates the need to concatenate criteria into helper columns — the function handles multi-column matching natively.

Edge Cases and What to Expect

• Out of stock: the function returns the if_not_found value cleanly — no #N/A errors to handle

• Resetting assignments: use the Reset All Matches button in the add-in settings panel to release all claims and force recalculation

• Single cell reassignment: delete the formula in one cell and re-enter it — that cell’s claim is released and it gets the next available match

• Workbook sharing: state is stored in workbook settings, so it persists when the file is saved and reopened

Key takeaway xmatchexclusive turns inventory allocation from a fragile formula problem into a one-function solution. The stock range is the pool, the formula is the reservation system, and out-of-stock is handled gracefully.

Getting Started in 3 Steps

1. Install XMatchExclusive from Microsoft AppSource

https://marketplace.microsoft.com/en-us/product/WA200010158

2. The function is immediately available as =XME.XMATCHEXCLUSIVE() in any workbook

3. Free tier supports up to 100 rows and 200 calls per day — enough to evaluate on a real dataset

The Free tier requires no sign-in. Activate Pro by entering your AppSource purchase email in the add-in settings panel for unlimited rows and calls.