A lookup finds something in a list and returns a value from the same row: the price of a product, the team of an employee. =XLOOKUP(what to find, where to look, what to return): =XLOOKUP(D2,A2:A5,B2:B5) finds D2 in column A and gives back the matching cell from column B.
If nothing matches you get #N/A. Add a fourth input to show something friendlier: =XLOOKUP(D2,A2:A5,B2:B5,"Not found").
When you copy a lookup down, the list must stay put, so lock it with $: =XLOOKUP(D2,$A$2:$A$5,$B$2:$B$5).
XLOOKUP is in Excel 2021 and Microsoft 365, and in Google Sheets. Older Excel files use VLOOKUP(what, table, column number, FALSE): it looks in the first column of the table and returns the column you number, counting from 1. Always end it with FALSE for an exact match; without it, VLOOKUP assumes the list is sorted and can quietly return the wrong row.
Practice
Fill in each order's price from the price list in E2 (copied to E4).
A
B
C
D
E
1
Product
Price
Order
Price
2
Apple
0.5
Pear
3
Banana
0.25
Apple
4
Pear
0.8
Plum
5
Plum
0.6
E2
More info
Quick reference
XLOOKUP(find, look in, return, [if missing])
The matching value from another column.
VLOOKUP(find, table, column, FALSE)
The older lookup; FALSE for an exact match.
INDEX(range, row) / MATCH(find, range, 0)
Another classic pair: MATCH finds the position, INDEX returns it.
The practice answers
Other formulas that give the same results count too.
Fill in each order's price from the price list in E2 (copied to E4). =XLOOKUP(D2,$A$2:$A$5,$B$2:$B$5)
Which team is the person with the ID in B7? Look it up in B8. =XLOOKUP(B7,A2:A6,C2:C6)
Kiwis aren't on the price list. Show "Not sold" instead of an error, in E2 (copied to E4). =XLOOKUP(D2,$A$2:$A$5,$B$2:$B$5,"Not sold")
The same search the older way: find the name for the ID in B7 with VLOOKUP, in B8. =VLOOKUP(B7,A2:C6,2,FALSE)
An unofficial course. Not made by or affiliated with Microsoft; Excel is a trademark of Microsoft Corporation.