LOOKUP in Google Sheets searches a sorted row or column and returns a corresponding result. It uses an exact match when available; otherwise, it uses the largest key below the search value.
Look Up a Price from Quantity Breakpoints
Enter this dataset starting in A1. Leave cells marked “(blank)” empty.
| Minimum qty | Unit price |
|---|---|
| 1 | 10 |
| 10 | 8 |
| 25 | 6 |
| 50 | 5 |
Enter this formula in E2:
=LOOKUP(20,A2:A5,B2:B5)A quantity of 20 returns the unit price 8, using the breakpoint 10. It has not reached the next breakpoint, 25. Keep the quantity column sorted from smallest to largest.
LOOKUP Function Syntax
=LOOKUP(search_key, search_range, result_range)The search range is one row or column. The result range should have the same number of positions, aligned with the search keys.
The result can be text or a number, including from a column to the left.
The two-argument form combines the search and result areas:
=LOOKUP(search_key, search_result_array)If the block has more columns than rows, LOOKUP searches its first row and returns from its last row. Otherwise, it searches the first column and returns from the last column.
LOOKUP has no exact-only setting. A missing key can return a neighboring band. Use VLOOKUP with FALSE or XLOOKUP when an absent ID must be reported as missing.
Check Exact Matches and Boundary Values
| Formula | Result | Reason |
|---|---|---|
| =LOOKUP(25,A2:A5,B2:B5) | 6 | Exact breakpoint |
| =LOOKUP(100,A2:A5,B2:B5) | 5 | Uses the highest breakpoint |
| =LOOKUP(0,A2:A5,B2:B5) | #N/A | Below the smallest breakpoint |
The returned value comes from the result range. The function does not simply return the search threshold itself. An exact duplicate key uses the last matching occurrence in the sorted lookup data.
Return a Letter Grade
Enter this dataset starting in A12. Leave cells marked “(blank)” empty.
| Minimum score | Grade |
|---|---|
| 0 | F |
| 60 | D |
| 70 | C |
| 80 | B |
| 90 | A |
=LOOKUP(87,A13:A17,B13:B17)The result is B, because 80 is the highest threshold that 87 reaches. This works for lower-bound grade bands. Sort the complete table together so thresholds and labels remain paired.
Use the Array Form to Return the Last Column
=LOOKUP(20,A2:B5)This returns 8, just like the three-argument price formula. The block has four rows and two columns, so LOOKUP searches column A and returns from column B.
In a larger vertical block, the last column is the return column. Use separate search and result ranges when you need a different output column or want the direction to be explicit.
Assign a Shipping Fee from Weight Cutoffs
Enter this dataset starting in A23. Leave cells marked “(blank)” empty.
| Minimum kg | Shipping fee |
|---|---|
| 0 | 3 |
| 1 | 5 |
| 5 | 9 |
| 10 | 15 |
=LOOKUP(6,A24:A27,B24:B27)A 6 kg parcel returns a shipping fee of 9, using the 5 kg lower boundary. This assumes each cutoff is the minimum weight for its band, not the maximum weight allowed.
Check the meaning of your thresholds before copying the pattern. A tariff based on upper limits needs a different matching rule; otherwise, values between cutoffs can receive the wrong fee.
Look Up Across a Horizontal Row
Enter this dataset starting in A33. Leave cells marked “(blank)” empty.
| Q1 | Q2 | Q3 | Q4 |
|---|---|---|---|
| 40 | 50 | 60 | 70 |
=LOOKUP("Q3",A33:D33,A34:D34)The result is 60. The labels are sorted left to right, and each sales total sits underneath its matching quarter. The two-argument formula =LOOKUP("Q3",A33:D34) also returns 60 because the block is wider than tall.
LOOKUP does not sort the cells for you. Unsorted keys can return an incorrect result without an error. Sort complete records or use an exact-match alternative appropriate to the task.
Other Google Sheets articles you may also like