Lesson 5: Match a key and return its value
Suppose A2:A5 contains product IDs P-10, P-11, P-12, P-13, B2:B5 contains prices 12, 18, 9, 25, and E2 contains P-12. Exact match is usually the right choice for IDs.
Modern exact lookup: =XLOOKUP(E2,A2:A5,B2:B5,"Not found") returns 9.
Lookup and reference formula reference
20 formulas shown
| Function | Purpose | Example |
|---|---|---|
| XLOOKUP | Looks up a value and returns a corresponding item; exact match by default. | =XLOOKUP(E2,A2:A5,B2:B5,"Not found"). |
| VLOOKUP | Looks down the first column of a table; return column is numbered. | =VLOOKUP(E2,A2:B5,2,FALSE). |
| HLOOKUP | Looks across the first row of a table. | =HLOOKUP(B1,A1:D3,3,FALSE). |
| LOOKUP | Legacy vector lookup; assumes sorted data for approximate matching. | =LOOKUP(E2,A2:A5,B2:B5). |
| INDEX | Returns the value at a row/column position in a range. | =INDEX(B2:B5,3) returns 9. |
| MATCH | Returns a position; use 0 for exact match. | =MATCH(E2,A2:A5,0) returns 3. |
| INDEX + MATCH | Flexible lookup that can return from either side. | =INDEX(B2:B5,MATCH(E2,A2:A5,0)). |
| XMATCH | Modern position lookup with exact match by default. | =XMATCH(E2,A2:A5) returns 3. |
| CHOOSE | Returns an item by numeric position. | =CHOOSE(2,"North","South","West") returns South. |
| CHOOSECOLS | Returns specified columns from an array. | =CHOOSECOLS(A1:D10,1,4). |
| CHOOSEROWS | Returns specified rows from an array. | =CHOOSEROWS(A1:D10,1,-1) returns first and last rows. |
| ROW / COLUMN | Returns a row or column number. | =ROW(B7) returns 7. |
| ROWS / COLUMNS | Counts rows or columns in a reference. | =ROWS(A2:A100) returns 99. |
| ADDRESS | Returns a cell address as text. | =ADDRESS(3,2) returns $B$3. |
| INDIRECT | Turns reference text into a reference; volatile. | =INDIRECT("B"&E2) reads a dynamically named cell. |
| OFFSET | Returns a shifted range reference; volatile. | =SUM(OFFSET(A1,1,0,5,1)). |
| TRANSPOSE | Rotates rows and columns; spills in Microsoft 365. | =TRANSPOSE(A1:C2). |
| HYPERLINK | Creates a clickable link with a friendly label. | =HYPERLINK("https://example.com","Open site"). |
| FORMULATEXT | Returns a cell's formula as text. | =FORMULATEXT(B2). |
| GETPIVOTDATA | Retrieves a summarized value from a PivotTable. | =GETPIVOTDATA("Sales",$A$3,"Region","West"). |
Choose a lookup pattern
- Use XLOOKUP for new workbooks when the target Excel version supports it.
- Use INDEX/MATCH when you need broad compatibility or independent row and column matching.
- Use VLOOKUP with FALSE for exact matching in older workbooks; the lookup column must be first.
- Wrap a not-found case with an explicit message; avoid approximate matches on unsorted IDs.
Progress stays in this browser.