VLOOKUP vs LOOKUP in Excel

The main difference in VLOOKUP vs LOOKUP is the match type. VLOOKUP can find an exact match, so it is the safe choice for IDs, names and codes. LOOKUP only finds an approximate match: the closest value that is less than or equal to what you search for, in data sorted from smallest to largest. This guide is for anyone who has met both in a spreadsheet or a job test and wants to know which one to trust.

VLOOKUP vs LOOKUP at a glance#

VLOOKUP is the general-purpose lookup. LOOKUP is a shorter, older function that only works safely on sorted data.

How each function works#

Both functions search for a value and bring back something from the same row. The difference is how they search.

VLOOKUP looks down the first column of a table and returns a value from a column to the right. You tell it which column by number:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

The last argument decides the match type. FALSE means "find exactly this value". TRUE, or leaving it out, means "find the closest lower value". The VLOOKUP function guide explains each argument.

LOOKUP (the vector form) looks along one row or column and returns the value in the same position from a second range:

=LOOKUP(lookup_value, lookup_vector, [result_vector])

There is no match-type argument. LOOKUP always behaves like VLOOKUP with TRUE: it finds the largest value that is less than or equal to the lookup value. If the lookup value is smaller than everything in the list, it returns #N/A. LOOKUP also has an older array form, =LOOKUP(lookup_value, array), which is kept mainly for compatibility. VLOOKUP or XLOOKUP are clearer for the same job.

The same lookups solved both ways#

Here is a small product list, sorted by ID:

Task 1: the ID exists#

Find the price of ID 110:

=VLOOKUP(110,A2:C7,3,FALSE)
=LOOKUP(110,A2:A7,C2:C7)

Both return 120. LOOKUP is shorter because you point at the result column directly instead of counting columns.

Task 2: the ID is missing#

Now search for ID 111, which isn't in the list:

=VLOOKUP(111,A2:C7,3,FALSE)
=LOOKUP(111,A2:A7,C2:C7)

VLOOKUP returns #N/A, which tells you the ID doesn't exist. LOOKUP returns 120, the price of the Shelf (ID 110), because 110 is the largest ID that is not bigger than 111. Nothing on the sheet warns you. For IDs, invoice numbers and names, that silent wrong answer is the biggest risk with LOOKUP.

Task 3: tiers and bands#

Approximate matching is exactly what you want for tiers. Say F2:G5 hold commission bands:

For sales of 32,000:

=VLOOKUP(32000,F2:G5,2,TRUE)
=LOOKUP(32000,F2:F5,G2:G5)

Both return 6%, because 25,000 is the highest band that 32,000 has reached. Here the two functions do the same job. The article on VLOOKUP TRUE vs FALSE shows more tier examples and the traps of approximate matching.

When to use VLOOKUP#

Use VLOOKUP whenever the value must match exactly, or when you are not sure the data is sorted. It is the better choice when:

• You look up IDs, codes, emails or names. FALSE gives you either the right row or a clear #N/A.
• The data arrives unsorted, such as an export from another system. VLOOKUP with FALSE doesn't care about order.
• A test or an interviewer asks for it by name. VLOOKUP is still one of the most tested functions; see these VLOOKUP interview questions.
• You share the file widely. Most Excel users can read a VLOOKUP formula.

Its main weakness is that it only returns columns to the right of the search column, and the column number doesn't update if someone inserts a column. If you need to look left, see VLOOKUP vs INDEX MATCH.

When to use LOOKUP#

Use LOOKUP when an approximate match on sorted data is what you want, or for two classic tricks. It fits well when:

• You place values into bands, such as grades, tax brackets or discount tiers, and the band table is sorted.
• The result column is to the left of the search column. LOOKUP takes a separate result range, so it can point anywhere, as long as the search range is sorted.
• You need the last number in a column. =LOOKUP(9.99E+307,B:B) returns the last number in column B, because no real number is bigger than the one you search for.
• You need the last match for a condition. =LOOKUP(2,1/(B2:B9="Chair"),C2:C9) returns the value in C from the last row where B is Chair. Each matching row becomes 1 and every other row becomes an error, which LOOKUP skips, so it lands on the last 1. This works in all versions without pressing Ctrl+Shift+Enter.

LOOKUP also works on a single row as well as a column. For a full horizontal table, compare VLOOKUP vs HLOOKUP.

Common mistakes with VLOOKUP and LOOKUP#

These slips cause most wrong lookup results:

1. Using LOOKUP on unsorted data. LOOKUP assumes the search range is sorted. If it isn't, it can return the wrong row with no error. Sort the range (Data > Sort Smallest to Largest) or use VLOOKUP with FALSE.
2. Leaving out FALSE in VLOOKUP. Without it, VLOOKUP uses an approximate match, just like LOOKUP, and has the same silent-error problem.
3. Result range a different size. In LOOKUP, A2:A7 with C2:C9 can return values from the wrong rows. Make both ranges start and end on the same rows.
4. Searching for a value smaller than the first one. =LOOKUP(99,A2:A7,C2:C7) returns #N/A, because no ID is 99 or lower. Bands should start at 0.
5. Hard-coded column numbers. In VLOOKUP, inserting a column inside the table can shift column 3 to the wrong data. Check lookups after changing the layout.

How XLOOKUP replaces both#

In Excel 2021 and Microsoft 365, XLOOKUP does what VLOOKUP and LOOKUP do, with fewer traps. It defaults to an exact match, takes a separate result range like LOOKUP, and can search from the bottom:

=XLOOKUP(110,A2:A7,C2:C7)

This returns 120, and a missing ID returns #N/A instead of a nearby row. For tiers, add a match mode of -1 (exact match or next smaller item): =XLOOKUP(32000,F2:F5,G2:G5,,-1) returns 6%. To get the last match, set the search mode to -1. If your file must open in older Excel, stick with VLOOKUP and LOOKUP. If you're deciding between lookup and a conditional total instead, see VLOOKUP vs SUMIF.

Our recommendation#

Make VLOOKUP with FALSE your default for anything that must match exactly: IDs, names and codes. Use LOOKUP only on sorted data, for tier tables and for the "last value" tricks, where its approximate match is a feature rather than a risk. If everyone who opens the file has Excel 2021 or Microsoft 365, use XLOOKUP for both jobs.

In short: VLOOKUP for exact matches, LOOKUP for sorted bands and last values.

Practise it#

Try the exercise on this page first: it is an exact-match lookup, so FALSE matters. Then practise VLOOKUP exact match in a live spreadsheet, where you pull prices from a catalog and fill the formula down. After that, practise approximate lookups for commission bands to see when a closest-lower match is the right answer.