Excel VLOOKUP: What is VLOOKUP and How to use it in Excel with examples

VLOOKUP is probably Excel’s most well-known function for both good and bad reasons. For many Excel users, successfully utilising VLOOKUP is a rite of passage. It is simple to use and performs a very useful function. Watching VLOOKUP scan a table, find a match, and return a correct result is incredibly satisfying for new users. 

What isVLOOKUP

The Vlookup function in Microsoft Excel lets you search for a value in a table or range of cells and return the corresponding value. There are four options available:

1. The value you want to search for (also known as the lookup value)
2. The range of cells where you want to search for the lookup value
3. The column number in the range where the return value is located
4. A logical value indicates whether you want an exact or approximate match of the lookup value.

VLOOKUP formula and How to use it in Excel

This is the acronym for Vertical Lookup in Excel, a built-in function that enables you to search for a specific value vertically across a sheet. Although VLOOKUP in Excel may appear complicated, when you give it a try, you will discover that it is a very simple and useful tool. To comprehend VLOOKUP, look at the example below.

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

lookup_value: This specifies the value that you want to look up in our data.

table_array: This is the location where the values are present in excel.

col_index_number: This specifies the column number from where we need to return the value.

range_lookup: This has two options; if the value is set to FALSE, that means we are looking for an exact match. If the value is TRUE, then we are looking for an approximate match.

Here is an example of how to use the VLOOKUP function:

How to find an exact match using VLOOKUP

For text-related Vlookups that require an exact match, the lookup_value can contain the following wildcard characters:

1. Matches any single character  
2. Matches any sequence of characters

VLOOKUP Requiring an Exact Match

An inventory of grocery items and their prices are listed in columns A and B of the spreadsheet. The Vlookup function is used in cell E2 of the spreadsheet to look up the price of an item.

The above Vlookup function returns the price for “Cornflakes”, which is $3.50.

In this example:               

•   The lookup_value is the text string “Cornflakes”, which is located in cell D2;
•   The table_array is defined as column A-B of the spreadsheet;
•   The col_index_num is set to 2 to denote that the value returned should be taken from column 2 of the table_array;
•   The [range_lookup] argument is set to FALSE to indicate that we only want a result to be returned if an exact match to the lookup_value is found.

Note that in this example, as the [range_lookup] argument is set to FALSE (requiring an exact match), it is not necessary to order the table_array by its first column.

How To Find an Approximate Match Using VLOOKUP?

To find an approximate match using the VLOOKUP function, you need to set the fourth argument of the function (range_lookup) to TRUE (1). Here’s how you can use the VLOOKUP function to find an approximate match:

1. A cell, enter the following formula: =VLOOKUP(lookup_value, table_array, col_index_num, 1).

2. Replace lookup_value with the value you’re searching for.

3. Replace table_array with the range of cells that contains the data you want to search.

4. Replace col_index_num with the column number of the data you want to return.

5. Press Enter to see the result.

How to Use VLOOKUP for Multiple Criteria?

When using the VLOOKUP function, you can only search for one criterion at a time. However, you can use an array formula or a combination of functions to perform a VLOOKUP with multiple criteria. Here are two common methods to do this:

Array Formula:

A VLOOKUP formula with multiple criteria can be performed by using an array formula. An array formula can return several results. It is created by pressing the keyboard shortcut “Ctrl + Shift + Enter” after you enter the formula.

The following is an example of an array formula that performs a VLOOKUP with multiple criteria:

=INDEX(B2:D7, MATCH(1, (A2:A7=E2)*(B1:D1=F2), 0), 3)

With this formula, the MATCH function returns the row number that meets both criteria: column A has a value equal to cell E2, and row 1 has a value equal to cell F2. In this case, column 3 is the column where the INDEX function returns the value.

Tips for using VLOOKUP effectively

In Microsoft Excel, “VLookup” is a common function that searches for specific data in a table and returns the corresponding value from the same row. Here are some tips for using this function:

1. Organise your data: Before using the VLookup function, make sure your data is organised in a clear and consistent manner, with the lookup value in the first column and the data you want to return in a separate column.

2. Use an exact match: By default, VLookup performs an exact match search, so make sure the lookup value is exactly the same as the value in the first column of the table array. If the data is not an exact match, you may get an error or unexpected results.

3. Use the right data type: If your lookup value is a text string, make sure it’s in the same format as the data in the first column of the table array (i.e., with or without leading/trailing spaces, uppercase/lowercase letters, etc.).

 Take a look at the following example

In the example, you can see we have a movie dataset. Here, we used the VLOOKUP formula to extract data.

In this VLOOKUP formula:

Our lookup_value is 5, which is ID.

Our table array is B4:F12

Range lookup is set to FALSE for the Exact match.

The column index number is 5, which is Release Year.

Basically, we are telling the VLOOKUP function to search the table array with an ID 5.  And if you found that, then give the value presented in the column Release Year which has a column index number of 5.

The post Excel VLOOKUP: What is VLOOKUP and How to use it in Excel with examples appeared first on Pricebaba.com Daily.