XLOOKUP for buyers: match open POs to supplier confirmations in Excel
An Excel lesson for buyers: pull confirmed dates, quantities and prices from a supplier's export into your open orders, and flag what is late, short or repriced.
Most suppliers will send you a confirmation report if you ask: a spreadsheet of your open orders with the dates, quantities and prices they have committed to. The slow part is what comes next, going down your own open-order report line by line, finding each PO in their file and checking whether anything changed.
Excel can do that matching for you. This lesson builds it in about ten minutes, and once it is built, next week’s report takes one paste.
You need Excel for Microsoft 365, Excel 2021 or later, or Excel for the web, which all have XLOOKUP. There is a version for older Excel at the end.
What you are starting with
Two lists. Your open orders, exported from your ERP:
| PO | Line | Part | Qty | Unit price | Need date |
|---|---|---|---|---|---|
| 41877 | 1 | HP-220-C | 2,500 | $4.10 | 24 Oct |
| 41877 | 2 | HP-221-A | 1,000 | $3.85 | 24 Oct |
| 42011 | 1 | BP-6L-220 | 1,000 | $18.40 | 31 Oct |
And the supplier’s confirmation export:
| PO | Line | Confirmed date | Confirmed qty | Confirmed price |
|---|---|---|---|---|
| 41877 | 1 | 31 Oct | 2,500 | $4.10 |
| 42011 | 1 | 28 Oct | 800 | $18.95 |
Line 2 of PO 41877 is missing from the supplier’s file. That is a finding too.
Step 1: Turn both lists into tables
Click anywhere in your open orders and press Ctrl+T, then OK. On the Table Design tab, name the table Orders. Do the same with the supplier’s export and name it Confirmations.
Tables matter here for two reasons: formulas can refer to columns by name, which makes them readable, and a formula typed in one row fills the whole column automatically.
Step 2: Make sure the PO numbers match
The most common reason a lookup fails is that one file stores PO numbers as numbers and the other stores them as text. They look identical and never match.
To check, select a PO cell in each table. If one is right-aligned and the other left-aligned, they are different types. The quickest fix is to convert the text column to numbers: select it, and use Data > Text to Columns > Finish.
Step 3: Look up the confirmed date by PO and line
In the Orders table, add a column called Confirmed date and enter:
=XLOOKUP(1, (Confirmations[PO]=[@PO]) * (Confirmations[Line]=[@Line]), Confirmations[Confirmed date], "Not confirmed")What it does: each comparison in brackets checks every row of the supplier’s file and returns TRUE or FALSE. Multiplying them gives 1 only where both the PO and the line match. XLOOKUP finds that 1 and returns the confirmed date from the same row. If there is no match, it returns “Not confirmed”.
Press Enter and the formula fills down the table. Then format the column as a date.
Step 4: Bring across quantity and price
Add two more columns, Confirmed qty and Confirmed price, with the same formula pointing at a different return column:
=XLOOKUP(1, (Confirmations[PO]=[@PO]) * (Confirmations[Line]=[@Line]), Confirmations[Confirmed qty], "")=XLOOKUP(1, (Confirmations[PO]=[@PO]) * (Confirmations[Line]=[@Line]), Confirmations[Confirmed price], "")Step 5: Work out what changed
Three columns turn the lookups into answers.
Days late, positive when the supplier will deliver after you need it:
=IF(ISNUMBER([@[Confirmed date]]), [@[Confirmed date]] - [@[Need date]], "")Qty difference, negative when they confirmed less than you ordered:
=IF(ISNUMBER([@[Confirmed qty]]), [@[Confirmed qty]] - [@Qty], "")Price change, as a percentage; format the column as %:
=IF(ISNUMBER([@[Confirmed price]]), [@[Confirmed price]] / [@[Unit price]] - 1, "")Step 6: One status per line
Add a Status column:
=IF(NOT(ISNUMBER([@[Confirmed date]])), "Chase", IF([@[Days late]] > 0, "Late", IF(OR(N([@[Qty difference]]) < 0, N([@[Price change]]) > 0), "Check", "OK")))The N() around the last two is not decoration. When a cell is empty text (“”), Excel treats text as larger than any number, so "" > 0 is TRUE and every unconfirmed price would show as an increase. N() turns the empty text into 0 first.
For the sample data, you get:
| PO | Line | Days late | Qty difference | Price change | Status |
|---|---|---|---|---|---|
| 41877 | 1 | 7 | 0 | 0% | Late |
| 41877 | 2 | Chase | |||
| 42011 | 1 | -3 | -200 | 3% | Check |
Step 7: Make the exceptions stand out
Select the Status column, then Home > Conditional Formatting > Highlight Cells Rules > Text that Contains. Type Late and pick a red fill. Repeat for Chase (orange) and Check (yellow).
Then filter the Status column to hide “OK”. What is left is your list of calls to make.
Next week: one paste
When the next confirmation report arrives, delete the rows in the Confirmations table (keep the header) and paste the new export in. Every formula updates.
Snags you may hit
- Dates arrive as text. If the confirmed dates are left-aligned and the days-late column shows errors, select the column and use Data > Text to Columns, choosing Date in step 3 with the order your supplier uses (DMY or MDY).
- The supplier confirms a line twice. XLOOKUP returns the first match. If their file is in date order and you want the latest confirmation, add two arguments to search from the bottom up:
..., "Not confirmed", 0, -1). - Older Excel without XLOOKUP. Use INDEX and MATCH instead, and in Excel 2019 or earlier confirm it with Ctrl+Shift+Enter:
=IFERROR(INDEX(Confirmations[Confirmed date], MATCH(1, (Confirmations[PO]=[@PO]) * (Confirmations[Line]=[@Line]), 0)), "Not confirmed")Ten minutes to build, one paste a week to run, and you stop finding out about a pushed-out date on the day the part was due.
