Excel Supreme
Search

Excel track · Exercise 41 of 86 Paid

VLOOKUP and XLOOKUP on the same table

Read a price with either lookup and know which argument does which job.

Intermediate

Four products, one price column

Read-only data · screen 1 of 2 · answer in B7

In B7, return the unit price for the SKU typed in B6 using the table A1:C5.

ABC
1SKUProductUnit price
2BX-210Bracket2.4
3CX-330Clamp3.8
4DX-410Dowel1.15
5EX-500Extrusion9.6
6Which SKU?CX-330
7Unit price

Download this grid (.xlsx) Two tabs: the grid you see, and the answer.

B7

What it does

Both functions take the SKU in B6, which is CX-330, and return the price beside it. VLOOKUP wants the whole table A2:C5 plus a column number: 3 counts SKU, product, price and lands on 3.8. That number only means price while the table starts at column A.

XLOOKUP names its two columns instead, A2:A5 to search and C2:C5 to return. Insert a column, move the price, and the formula still points at the price.

The fourth argument in VLOOKUP is the one that gets forgotten. FALSE makes a near miss fail rather than pass, and on a column of SKU codes a near miss is another product at another price.

Syntax

=VLOOKUP(value, table, column, FALSE) · =XLOOKUP(value, search, return)

Worked example

On Four products, one price column, =VLOOKUP(B6,A2:C5,3,FALSE) returns 3.8.

=VLOOKUP(B6,A2:C5,3,FALSE)

Common errors

ErrorWhat it means and how to fix it
#N/AThe SKU is not in the searched column: a missing product, not a broken formula.
#REF!VLOOKUP's column number is wider than the table, which asks for a column the table does not have.

At work

A supplier price list pasted into a sheet, where the SKU column is the only reliable key.