# Excel LOOKUP vs VLOOKUP: With 3 Examples

### Method 1 – Dealing with Approximate Match

One of the main differences between LOOKUP and VLOOKUP functions is that the LOOKUP function is bound to an Approximate Match. VLOOKUP offers both Approximate and Exact matches.

LOOKUP automatically fetches approximate matches from lookup_arrar (i.e., B4:B16)

➤VLOOKUP offers an approximate or exact match option to fetch a value from col_index_num.

Performing LOOKUP Function

The formula we use in the Lookup result cell is

` =LOOKUP(H4,B4:B16,C4:C16)`

H4; is the lookup_value.

B4:B16; is the lookup_vector.

C4:C16; is the [result_vector].

From the dataset, return value for any random lookup_value (e.g., Chocolate). You don’t have any entries of that kind, so the LOOKUP formula still returns a value. The resultant value is wrong. The LOOKUP formula fetches the approximate value that matches the lookup_value (e.g., Chocolate).

Performing VLOOKUP Function

The Formula used in the Vlookup result cell is

`=VLOOKUP(H11,B4:E16,2,FALSE)`

H11; is the lookup_value.

B4:E16; is the table_array.

2; is the col_index_num.

FALSE; is the [range_lookup].

Like the LOOKUP formula, we use the VLOOKUP formula to fetch the resultant value from a selected column number. It returns #N/A as there is no such entry.

Both the LOOKUP and VLOOKUP functions in one picture give you a complete sense of the LOOKUP function’s limitation to approximate match.

Restricted by Default Approximate Match, the LOOKUP function lags behind the VLOOKUP function.

### Method 2 – Performing Both-Directional Operation

The LOOKUP function searches and matches values in both directions left to right and right to left. The VLOOKUP function only allows a left-to-right search to match. More specifically, for VLOOKUP, the lookup_value must be on the left of the columns from which it fetches the resultant values.

LOOKUP allows left-to-right or right-to-left operability. It matches lookup_value to rows or columns simultaneously.

➤VLOOKUP only allows left-to-right operability. It matches lookup_value to columns only.

Performing LOOKUP Function

The formula we use in the Lookup Result cell is

` =LOOKUP(H4,C4:C16,B4:B16)`

H4; is the lookup_value.

C4:C16; is the lookup_vector.

B4:B16; is the [result_vector].

In the dataset, return value for any random lookup_value (e.g.,57). We use the LOOKUP function to get the exact result (e.g., Bran). As the LOOKUP function operates in both directions, it can fetch the [result_vector].

Performing VLOOKUP Function

The formula we use in the Vlookup Result cell is

` =VLOOKUP(H11,B4:E16,1,FALSE)`

H11; is the lookup_value.

B4:E16; is the table_array.

1; is the col_index_num.

FALSE; is the [range_lookup].

Like the LOOKUP formula, the VLOOKUP formula fetches the resultant value from a selected column number (i.e.,1). It returns #N/A as it’s unable to look up for return value in columns that are left to the lookup_value. The col_index_num (i.e.,1) is left to the lookup_value column (i.e.,2).

Differentiate the LOOKUP and VLOOKUP functions’ directional operability just by looking at the image below.

The LOOKUP function is multidimensional, considering its operability where the VLOOKUP function stumbles.

### Method 3 – Interchangeable LOOKUP and VLOOKUP

Both LOOKUP and VLOOKUP functions generate lookup results in similar ways apart from looking directions. You can use them interchangeably in most cases.

The LOOKUP function is simple and returns values from looking into lookup_vector. The VLOOKUP function also does that, but in a complex way. It returns values from the column specified in the argument.

Performing LOOKUP Function

The formula used in Lookup Result is

`=LOOKUP(H4,B4:B16,C4:C16)`

H4; is the lookup_value.

B4:B16; is the lookup_vector.

C4:C16; is the [result_vector].

You get 57. Cross-checking the value in the Quantity column gives you the same entry as the result.

Say the LOOKUP formula returns with the correct result.

Performing VLOOKUP Function

The formula we use in the Vlookup Result cell is

` =VLOOKUP(H11,B4:E16,2,FALSE)`

H11; is the lookup_value.

B4:E16; is the table_array.

2; is the col_index_num.

FALSE; is the [range_lookup].

VLOOKUP returns 57. The resultant value is correct.

Find the interchanging behavior among the LOOKUP and VLOOKUP functions.

Performing both LOOKUP and VLOOKUP functions, both are similar in their offerings and provide the same results.

## Related Articles

<< Go Back to Excel VLOOKUP Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF