Function Problems

How to Use Excel XLOOKUP to Match Vendor Names and Prices

Algirdas Jasaitis Sep 27, 2026 869 views

Question details

The user wants to automatically transfer and match vendor prices from a dynamic monthly order sheet to a Vendor Paid sheet.

How to Use XLOOKUP to Match Vendor Names and Prices in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Organizing accounts payable by matching vendor names from a randomized, frequently changing monthly order list to a finalized payment sheet.
Observed behavior: Seeking a formulaic method to extract single prices, sum up multiple orders, or filter all records for specific vendors without manual data entry.

Before you start

Ensure that vendor names are spelled exactly the same across both the monthly order sheet and the Vendor Paid sheet to prevent formula mismatch errors.

Solution 1Recommended

Use XLOOKUP to Match Individual Vendor Prices

This is the best method if each vendor only has one order per month. XLOOKUP searches for the vendor name and returns the exact price from the order sheet.

XLOOKUP is a modern, flexible function that replaces older formulas like VLOOKUP. It defaults to an exact match and can easily handle missing data by allowing you to specify an 'if not found' value directly in the formula.

Select the destination cell

Open your Vendor Paid sheet and click on the cell where you want the vendor's price to appear (for example, cell B2).

Enter the XLOOKUP formula

Type the formula: =XLOOKUP(A2, Orders!A:A, Orders!B:B, ""). Here, A2 is the vendor name on your current sheet, Orders!A:A is the column containing vendor names in the order sheet, and Orders!B:B is the column containing the prices.

Apply to the rest of the list

Press Enter to see the result. Click the bottom-right corner of cell B2 and drag it down to apply this formula to all other vendors in your list.

Use XLOOKUP to Match Individual Vendor Prices

Handling Missing Vendors: The "" at the end of the formula ensures that if a vendor from your list didn't have an order this month, the cell will remain cleanly blank instead of displaying an ugly #N/A error.

Solution 2

Use SUMIF for Vendors with Multiple Orders

If a vendor has multiple separate orders on the monthly sheet, XLOOKUP will only return the first one it finds. Use SUMIF to calculate the total amount owed to the vendor.

Solution 3

Use FILTER to Extract All Matching Order Rows

If you need to see every individual order detail for a vendor rather than just a total sum, use the FILTER function to pull all matching rows.

Master Data Analysis with WPS Office

Use XLOOKUP and Advanced Formulas in WPS Spreadsheet

WPS Spreadsheet provides complete support for advanced data functions including XLOOKUP, SUMIF, and FILTER. You can effortlessly manage vendor payments, match complex datasets, and automate your financial records.

1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing your vendor orders.
2. Enter the function: Click your target cell, type =XLOOKUP(, and follow the intuitive on-screen tooltip to select your lookup value, lookup array, and return array.
3. Fill the column: Press Enter and double-click the fill handle to instantly match all vendor prices across your entire sheet.

100% compatible with Microsoft Excel formulas and file formatsIncludes modern array functions like XLOOKUP and FILTERFree, lightweight, and incredibly fast for large datasetsFamiliar tabbed interface makes migrating from Excel seamless

microsoft office alternative - wps office

Frequently Asked Questions

Why is my XLOOKUP returning a #N/A error when the vendor name is on the list?

This usually happens due to formatting inconsistencies, such as trailing spaces or spelling differences. Use the TRIM() function around your lookup value (e.g., =XLOOKUP(TRIM(A2),...)) to remove accidental spaces, and ensure both sheets are formatted identically.

Can XLOOKUP pull vendor prices from a completely different workbook?

Yes. You can reference another workbook by keeping both files open. When you are selecting your lookup and return arrays during formula creation, simply click over to the other workbook to select the columns. The file path will automatically be added to your formula.

How is XLOOKUP better than VLOOKUP for matching prices?

Unlike VLOOKUP, XLOOKUP defaults to an exact match, can search from right to left, and doesn't break if you insert or delete columns in your order sheet. It also includes a built-in argument for handling missing data, eliminating the need to wrap your formula in IFERROR.

What happens if I use XLOOKUP but there are two identical vendor names with different prices?

XLOOKUP will only return the very first match it encounters from the top down. If you need to combine the prices of multiple matching vendor entries, you should use the SUMIF function instead.

Excel & Data Problems