How to Use LOOKUP in Google Sheets

LOOKUP is one of Google Sheets’ oldest lookup functions, and it is still useful when you need a quick, simple way to find a value in a single row or column. Unlike VLOOKUP or HLOOKUP, LOOKUP has a simpler syntax and can work with either a one-dimensional range or a two-dimensional array. This guide walks you through the two forms of LOOKUP, shows you exactly how to write the formulas, and points out the common pitfalls so you can use it with confidence.

What You’ll Need

• A Google Sheets document with your data already entered. LOOKUP works best when your data is sorted in ascending order (A to Z, or smallest to largest number).
• Basic familiarity with typing formulas into a cell (starting with an equals sign).
• If you want to follow along with the examples, a blank sheet or a spare area to test the formulas.

Step-by-Step

There are two ways to use LOOKUP: the array form and the vector form. The vector form is more common and easier to understand, so we’ll start there.

1. Open your Google Sheet and click on the cell where you want the result to appear. This is where the LOOKUP formula will go.
2. Type the equals sign to begin your formula, then type LOOKUP. As you type, Google Sheets will show a suggestion; you can click it or keep typing.
3. Choose the vector form by typing an open parenthesis. The vector form syntax is: =LOOKUP(search_key, search_range, result_range). The array form is =LOOKUP(search_key, array) and we’ll cover that in a later step.
4. Enter the search key – this is the value you want to look up. It can be a number, text, or a cell reference. For example, if you want to find the price of an item named in cell A2, you would type A2 or the actual text in quotes like "Apple".
5. Add a comma to separate the arguments, then select the search range. This is the single row or column that contains the values you are searching in. For example, if your item names are in column A, you would select A:A or A2:A10.
6. Add another comma and select the result range. This is the row or column that contains the values you want to return. It must be the same size as the search range. For example, if your prices are in column B, select B:B or B2:B10.
7. Close the parenthesis and press Enter. The cell will now display the value from the result range that corresponds to the search key.
8. Test with a simple example. In column A, type: Apple, Banana, Cherry, Date. In column B, type: 1.20, 0.50, 2.00, 1.75. In cell D1, type =LOOKUP("Banana", A:A, B:B) and press Enter. You should see 0.50.
9. Use the array form when your data is in a single row or column and you want to return a value from the last row or column. The syntax is =LOOKUP(search_key, array). For example, if you have a list of names in A1:A5 and you want the last value that is less than or equal to your search key, you can use =LOOKUP("Charlie", A1:A5). This will return the last value in the range that is less than or equal to “Charlie” (assuming the range is sorted).
10. Understand how LOOKUP picks a match. If it finds an exact match, it returns that. If not, it finds the largest value in the search range that is less than or equal to the search key. This is why sorting is important – if your data is not sorted, the result may be unpredictable.
11. Use LOOKUP with numbers. For example, if you have a table of tax rates by income bracket, you can use LOOKUP to find the rate for a given income. In column A (sorted ascending): 0, 10000, 20000, 30000. In column B: 0%, 10%, 20%, 30%. To find the rate for an income of 15000, use =LOOKUP(15000, A:A, B:B). It will return 10% because 15000 is greater than 10000 but less than 20000.
12. Combine LOOKUP with other functions. You can nest LOOKUP inside IF, or use it with CONCATENATE to build dynamic lookups. For example, =IF(LOOKUP(A2, D:D, E:E)="", "Not found", LOOKUP(A2, D:D, E:E)) can handle missing values.
13. Copy the formula down if you need to apply it to multiple rows. Use absolute references (like $A$2:$A$10) if you don’t want the ranges to change as you copy.

Common Mistakes

• Not sorting the data. LOOKUP assumes your search range is sorted in ascending order. If it isn’t, you may get wrong results or even a #N/A error. Always sort your data or use a different function like VLOOKUP with FALSE as the last argument.
• Mixing row and column orientations. If your search range is a column (vertical), your result range must also be a column. If you mix a column search with a row result, you’ll get a #REF! error.
• Using different-sized ranges. The search range and result range must have the same number of cells. If they don’t, you’ll get a #N/A error.
• Forgetting that text is case-insensitive. LOOKUP does not care about uppercase or lowercase. “apple” and “Apple” are treated the same. This is usually fine, but it can cause surprises if you expected case-sensitive matching.
• Using LOOKUP when you need an exact match. If your data is not sorted and you need an exact match, LOOKUP can give you a wrong result. Use VLOOKUP or INDEX/MATCH with the appropriate match type instead.

Tips

• Sort your data before using LOOKUP. This is the single most important tip. Use Data > Sort sheet by column to sort your search range.
• Use a helper column if your data is not naturally sorted. For example, you can create a column that combines two values, sort by that, and then use LOOKUP.
• Use LOOKUP for approximate matches. It’s perfect for tiered pricing, tax brackets, or any “find the closest value” scenario.
• Consider using VLOOKUP or INDEX/MATCH when you need an exact match or when your data is not sorted. They are more flexible and less error-prone.
• Use cell references for the search key instead of typing values directly. That way, if the value changes, the formula updates automatically.
• Test with simple data first to make sure you understand how LOOKUP behaves before applying it to your real dataset.

Troubleshooting

I get a #N/A error. This usually means the search key is smaller than the smallest value in the search range, or the ranges are not aligned. Check that your search range is sorted and that your result range is the same size. If your search key is text, make sure it exists in the range (remember it’s case-insensitive).

I get a #REF! error. This means your ranges are not the same size or orientation. For example, if your search range is a column (A2:A10) and your result range is a row (B1:K1), you’ll get #REF!. Make sure both ranges are the same shape and size.

The result is wrong, but no error appears. This is almost always because your data is not sorted. LOOKUP assumes a sorted list. Sort your data and try again. Also, double-check that you selected the correct result range – it’s easy to accidentally select the wrong column.

LOOKUP returns a value from the wrong row. This happens when there are duplicate values in your search range. LOOKUP will return the last match (the one closest to the bottom) in a sorted list. If you need the first match, use VLOOKUP with FALSE or INDEX/MATCH.

I’m trying to look up a number but it’s stored as text. Sometimes numbers are imported as text. Use the VALUE function on the search key or convert the data to numbers using Data > Split text to columns or by multiplying by 1.

Frequently Asked Questions

What is the difference between LOOKUP and VLOOKUP? LOOKUP is simpler and can search a single row or column. VLOOKUP searches a column and returns a value from a specified column to the right. VLOOKUP also allows you to specify whether you want an exact or approximate match, while LOOKUP always does an approximate match (with sorted data).

Does LOOKUP work with wildcards? No, LOOKUP does not support wildcards like * or ?. If you need wildcard matching, use VLOOKUP with the search key containing a wildcard and the match mode set to 2 (exact match with wildcards).

Can I use LOOKUP to search from right to left? Yes, LOOKUP is not restricted to left-to-right like VLOOKUP. You can place your search range anywhere and your result range anywhere, as long as they are the same size and orientation.

Why does LOOKUP return the last value in the range when there is no match? Because LOOKUP does an approximate match. If your search key is larger than any value in the search range, it will return the last value. If it is smaller than the first value, you get #N/A. To avoid this, ensure your search key falls within the range of the search range.

Is LOOKUP faster than VLOOKUP? In most cases, the performance difference is negligible for typical spreadsheet sizes. LOOKUP may be slightly faster because it has less overhead, but the difference is not noticeable unless you have tens of thousands of rows.

Can I use LOOKUP to return a value from a row above the search range? No, LOOKUP only works with a single row or column. If you need to search a table and return a value from a different row, use INDEX and MATCH.

Related

• Fix iPhone Restore Error 3194 on Windows 10
• Fix DNS Server Isn’t Responding in Windows 11
• Fix Wi-Fi Keeps Disconnecting on Windows 11