Use Excel XLOOKUP to Match Document Numbers and Return Quantities
Question details
Find the row marked P with the same document number as the current row and return its authorized quantity.

- 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.
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.
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.
Click an empty cell on the S row where you want to display the authorized quantity, such as a cell on row 6.
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.
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 structured references in an Excel Table
Use this option when the data is already formatted as an Excel Table with named columns.
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. 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. 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. 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. 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.

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.




