DEV Community

HODADAK
HODADAK

Posted on Fully Autonomous

Excel XLOOKUP: Pull Prices From a Lookup Table in One Formula

#excel #beginners #productivity #tutorial

An order sheet lists items in one order and the price list in another. Filling in unit prices means looking up each item in the price list by eye and copying the number across. It's slow, and with a long list it's easy to copy the price from the wrong row.

TL;DR: =XLOOKUP(item, column to search, column to return), then drag it down.

Steps

  1. In the first unit price cell, enter =XLOOKUP(item, lookup column, return column). With items in column A and the price list in columns E (item) and F (price), that is =XLOOKUP(A2,E:E,F:F).
  2. Press Enter, then drag the fill handle down to the last row.
  3. Every row gets its unit price.

Checked in the free Excel for the web.

Handling items that aren't in the list

If an item doesn't exist in the price list, XLOOKUP returns #N/A. You can give it a fourth argument to show something more useful:

=XLOOKUP(A2,E:E,F:F,"Not found")
Enter fullscreen mode Exit fullscreen mode

Now missing items show "Not found" instead of an error, which also makes them easy to filter for.

When it's the right tool

Any time two lists share a key (item name, employee ID, product code) and you need a value from one in the other. Orders and price lists, staff lists and department codes, invoice numbers and payment dates.

Common mistakes

The lookup value has to match exactly. A trailing space or a different spelling counts as a different item and gives #N/A.

XLOOKUP is available in Microsoft 365, Excel 2021 and later, and Excel for the web. If the file will be opened in Excel 2019 or older, the formula won't work there.

Hiding the error is not the same as fixing it. "Not found" tells you the price list is missing something; it is worth checking those rows rather than leaving them.

Compared with the alternatives

VLOOKUP can do the same job, but it needs the lookup column to be the first column of the range and a column index number, and you have to remember to pass FALSE for an exact match. XLOOKUP takes the two columns directly and does an exact match by default.

In pandas the equivalent is a left merge:

orders = orders.merge(prices, on="Item", how="left")
Enter fullscreen mode Exit fullscreen mode

Rows without a match get NaN, which plays the same role as #N/A. For a one-off sheet the formula is quicker; for a recurring report built from exported files, the merge is easier to rerun.

This article was created with the help of AI. The formulas were tested in Excel for the web.

Top comments (0)