VLOOKUP: data from another table
An order export from a CRM has each product's item number, but not its price. The prices are only in the price list on the right:
By hand, you'd have to take the item number, find it in the price list and copy the price from the same row. Forty times in a row, and one typo ruins the whole calculation.
The VLOOKUP function does this work. It takes a value, finds it in the first column of another table and returns what's in the same row. The value you search by is called the key, and the table you search in is the lookup table. Here the key is the item number, and the lookup table is the price list.
The four parts of the formula
The general form of VLOOKUP looks like this:
=VLOOKUP(key,table,column_number,0)- key
What we're looking for. In the export, it's the order's item number. In Excel's tooltip, this argument is called lookup_value.
- table
Where we're looking: the table without the header row, from the first item number to the last price. VLOOKUP looks for the key only in its leftmost column, so the table starts with the item number column. In Excel's tooltip, it's table_array.
- column_number
Which column of the table to take the answer from. You count from the left edge of the table, not of the sheet.
- 0
An exact match is required. If the key isn't in the table, the cell shows #N/A, "not available", and you see the gap right away. In Excel's tooltip, it's range_lookup, and you almost always write 0 here. You use 1 only for scales, more on them below.
For the item number in A2, the price formula looks like this:
=VLOOKUP(A2,$D$2:$F$6,3,0)
It reads like this: "find A2 in the first column of the table D2:F6 and return the value from its third column; only an exact match will do." The $ signs keep the table from shifting when you drag the formula. More on them below.
The column number is counted from the table
The price is in column F, the sixth on the sheet, but in the table D:F this column is the third: the number is counted from the left edge of the table.
If you move the price list to another place, the range in the formula changes, but the number stays the same.
Locking the table
The table is locked with $ signs at both ends: $D$2:$F$6. Without them, the table moves down along with the formula when you drag it, like any address, and the top rows of the price list drop out of the search.
On the sheet below, the formula was dragged without $, and some prices turned into errors. Fix it:
The whole export at once
Back to the forty orders. Type the same price formula in B2 and double-click the fill handle in the lower right corner of the cell: Excel fills it down to the last order by itself.
Forty prices from one formula. Next week's export is filled the same way: a formula in B2 and a double-click on the fill handle.
A scale: when you write 1 at the end
A bulk discount is often given on a scale based on the order amount:
- under $10,000, no discount;
- from $10,000, a 5% discount;
- from $50,000, a 10% discount;
- from $100,000, a 15% discount.
There's no order of exactly $23,500 in this scale, and with 0 as the last argument VLOOKUP returns #N/A. But you need the "from $10,000" row: 23,500 is more than 10,000 but less than 50,000.
For this, the scale is written as a lookup table: the first column holds the lower bounds in ascending order, starting with 0, and next to each one is the discount from that bound. Here you write 1 as the last argument of VLOOKUP:
=VLOOKUP(B2,$E$2:$F$5,2,1)
With 1 at the end, VLOOKUP looks for an approximate match: it takes the largest bound that isn't greater than the order amount. For 23,500 that's 10,000, and the formula returns 5%. Bonuses for years of service or for sales volume are calculated on a scale the same way.
The bounds in a scale are always sorted in ascending order, from smallest to largest: that's the order VLOOKUP with 1 relies on to find the right row.
If the answer is left of the key: XLOOKUP
VLOOKUP looks for the key only in the leftmost column of the table, so the answer can only be to the right of it. Sometimes it's the other way around: in a warehouse table the item number is on the left and the name on the right, but you need to find the item number by the name. That's what the XLOOKUP function is for. You give it the column to search in and the column to take the answer from separately:
=XLOOKUP(A2,$F$2:$F$6,$E$2:$E$6,"not in list")
The function looks for the key from A2 in the name column F2:F6 and takes the answer from the same row of the item number column E2:E6. It doesn't need a column number, and it looks for an exact match on its own, without 0. The fourth argument is optional: its text appears instead of #N/A if the key isn't in the table. For VLOOKUP, the IFERROR function from the lesson "Why VLOOKUP returns #N/A" does the same thing.
XLOOKUP is available in Excel 2021 and Microsoft 365; older versions don't have it.