Function Problems

Use Excel XLOOKUP to Match Document Numbers and Return Quantities

Muhammad TalhaMuhammad Talha Nov 18, 2026 869 views

Question details

Find the row marked P with the same document number as the current row and return its authorized quantity.

Use Excel XLOOKUP to Match Document Numbers and Return Quantities
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet contains P and S rows, document numbers, and authorized quantities. An S row needs the quantity from the matching P row.
Observed behavior
The desired result is the authorized quantity from the first P row whose document number matches the current row.
Before you start

Confirm that column D contains the P/S status, column N contains document numbers, and column H contains authorized quantities. Adjust the references if your columns or data rows differ, and check that matching document numbers use consistent formats.

Solution 1Recommended

Use XLOOKUP with a combined status and document number

Use this formula in a version of Excel that supports XLOOKUP to return the quantity from the first matching P row.

1
Select the result cell

Click an empty cell on the S row where you want to display the authorized quantity, such as a cell on row 6.

2
Enter the lookup formula

In the formula bar, enter =XLOOKUP("P"&N6,$D$2:$D$1000&$N$2:$N$1000,$H$2:$H$1000,0) and press Enter. Change the range endpoints to cover your data.

3
Copy the formula

Drag the cell's fill handle down the result column for the other rows. The N6 reference changes for each row while the lookup ranges remain fixed.

Use XLOOKUP with a combined status and document number
No match or duplicate matches: The formula returns 0 if no matching P row is found. If several P rows have the same document number, XLOOKUP returns the quantity from the first match.
Match document quantities in WPS Spreadsheet

Return Matching P-Row Quantities in WPS Spreadsheet

Use a helper column with INDEX and MATCH in WPS Spreadsheet to find the first P row for each document number and return its authorized quantity.

  1. 1. Open the workbook: Open the Excel workbook in WPS Spreadsheet and confirm that D contains the status, N contains the document number, and H contains the authorized quantity.
  2. 2. Create a helper key: Choose an empty column, such as O, and enter =D2&"|"&N2 in O2. Drag the fill handle down through the data rows.
  3. 3. Enter the lookup formula: In an empty result cell on row 2, enter =IFERROR(INDEX($H$2:$H$1000,MATCH("P|"&N2,$O$2:$O$1000,0)),0). Adjust the ranges to cover your data.
  4. 4. Fill the result column: Drag the formula's fill handle down for the remaining rows. The formula returns 0 when it cannot find a matching P key.
Match a P status and document number using a helper keyReturn the first matching authorized quantityOpen and edit Microsoft Excel XLSX and XLS filesWork with formulas in a familiar spreadsheet interface
microsoft office alternative - wps office

Frequently Asked Questions

How can I show the result only on S rows?

Wrap the lookup in an IF test. For row 6, enter =IF(D6="S",XLOOKUP("P"&N6,$D$2:$D$1000&$N$2:$N$1000,$H$2:$H$1000,0),"") in the result cell.

Why does the formula return 0?

The formula returns 0 when it finds no matching P status and document number. Check column D for the exact P value, compare document number formats in column N, and make sure the lookup ranges include the P row. A matching quantity of zero also returns 0.

What happens if a document has more than one P row?

XLOOKUP returns the authorized quantity from the first matching P row in the lookup range. It does not add quantities from multiple matches.

Can I use this formula in older versions of Excel?

XLOOKUP is not available in Excel 2016 or Excel 2019. You can use a helper column with INDEX and MATCH instead, as shown in the WPS Spreadsheet steps.