Excel LOOKUP vs VLOOKUP: With 3 Examples

Get FREE Advanced Excel Exercises with Solutions!

We use Both Excel LOOKUP and VLOOKUP functions to lookup values in datasets to bring out values from desired column and range. Though LOOKUP and VLOOKUP work similarly in their outcomes, they differ in operability. In this article, we discuss Excel LOOKUP vs VLOOKUP functions mentioning the differences and interchangeability between them.

Suppose, we have a dataset of Product Sales. We want to look up any value in selected columns or ranges using LOOKUP and VLOOKUP to demonstrate the differences among them.

Dataset-Excel Lookup vs Vlookup


Dataset for Download

You are welcome to download the workbook from the link below.


Basics of LOOKUP & VLOOKUP

LOOKUP Function:

The syntax of the LOOKUP function is

LOOKUP(lookup_value, lookup_vector, [result_vector])

Or

LOOKUP(lookup_value,array)

In the syntax,

lookup_value; the value you want to look for.

lookup_vector; the single row or column where the lookup_value exists.

[result_vector](Optional); equal size to lookup_vector, the single row or column from where the resultant value is extracted. The function returns 1st column data in case of its absence.

array; it extracts the value matching the lookup_value from the range.

Lookup function

VLOOKUP Function:

The syntax of the VLOOKUP function is

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

In the syntax,

lookup_value; the value you want to look for.

table_array; the table or range in where you search the lookup_value .

col_index_num; the column number from where the lookup_value is to be extracted.

[range_lookup]; declares lookup match status. TRUE-Approximate Match, FALSE-Exact Match.

Vlooup function

Read More: What Is a Table Array in VLOOKUP? (Explained with Examples)


Excel LOOKUP vs VLOOKUP Function

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. Where 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)

Here,

H4; is the lookup_value.

B4:B16; is the lookup_vector.

C4:C16; is the [result_vector].

Lookup approximate match-Excel Lookup vs Vlookup

From the dataset, we want a return value for any random lookup_value (i.e., Chocolate). But we don’t have any entries of that kind still the LOOKUP formula returns a value. Obviously, the resultant value is wrong. The LOOKUP formula fetches the approximate value matches with the lookup_value (i.e., Chocolate).

Performing VLOOKUP Function

The Formula used in the Vlookup result cell is

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

Here,

H11; is the lookup_value.

B4:E16; is the table_array.

2; is the col_index_num.

FALSE; is the [range_lookup].

Vlookup Approximate match

Similar to 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 LOOKUP and VLOOKUP function’s results in one picture give you a complete sense of the LOOKUP function’s limitation to approximate match.

lookup vs vlookup- approximate match

Now, it can be said that restricted by Default Approximate Match, the LOOKUP function lags behind the VLOOKUP function.

Read More: Why VLOOKUP Returns #N/A When Match Exists? (5 Causes & Solutions)


Similar Readings


2. Performing Both-Directional Operation

The LOOKUP function searches and matches values in both directions left to right or right to left. However, the VLOOKUP function does only left to right search to match. More specifically for VLOOKUP, the lookup_value must be in the left to the columns it fetches the resultant values from.

➤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)

Here,

H4; is the lookup_value.

C4:C16; is the lookup_vector.

B4:B16; is the [result_vector].

Lookup function operability

 In the dataset, we want a return value for any random lookup_value (i.e.,57). We use the LOOKUP function to come up with the result and it comes up with the exact result (i.e., Bran). As the LOOKUP function operates in both directions it’s able to fetch the [result_vector].

Performing VLOOKUP Function

The formula we use in the Vlookup Result cell is

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

Here,

H11; is the lookup_value.

B4:E16; is the table_array.

1; is the col_index_num.

FALSE; is the [range_lookup].

Vlookup function operability

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. Here, the col_index_num (i.e.,1) is left to the lookup_value column (i.e.,2).

You can simply differentiate the LOOKUP and VLOOKUP function’s directional operability just by looking at the image below.

Lokkup vs Vlookup-both directional comparison

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

Read More: 7 Types of Lookup You Can Use in Excel


3. Interchangeable LOOKUP and VLOOKUP

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

From the syntax, we can see the LOOKUP function is simple and returns values from looking into lookup_vector. The VLOOKUP function also does that but in a complex way. The VLOOKUP function 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)

In the formula,

H4; is the lookup_value.

B4:B16; is the lookup_vector.

C4:C16; is the [result_vector].

Lookup function

You get 57 as a result. If you cross-check the value in the Quantity column, you get the entry the same as the result.

So, simply we can say the LOOKUP formula returns with the right result.

Performing VLOOKUP Function

The formula we use in the Vlookup Result cell is

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

In the formula,

H11; is the lookup_value.

B4:E16; is the table_array.

2; is the col_index_num.

FALSE; is the [range_lookup].

Vlookup Function

 Same as the LOOKUP formula, VLOOKUP returns 57 as a result. And you simply say by looking that the resultant value is correct.

From the following picture, you can find the interchanging behavior among LOOKUP and VLOOKUP functions.

Lookup vs vlookup-interchangeability

Performing both LOOKUP and VLOOKUP functions, we simply say both are similar in their offerings and provide exact same results.

Read More: How to Lookup Multiple Values in Excel (10 Ways)


Conclusion

The LOOKUP and VLOOKUP are similar in providing results considering respective directions. Though the LOOKUP function is multidimensional and easier to apply than the VLOOKUP function. However, in the case of an exact match VLOOKUP function stands unique. Hope the above-discussed examples clarify your confusion. Comment if you have further inquiries or have something to add.


Related Articles

Maruf Islam
Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo