In Microsoft Excel, the **VLOOKUP **function is generally used to extract data based on a lookup value in a column or a range of cells. In this article, you’ll be introduced to the 10 best examples and practices with the **VLOOKUP** function.

**Introduction to the VLOOKUP Function**

**Function Objective:**

The **VLOOKUP function** is used to look for a given value in the leftmost column of a given table and then returns a value in the same row from a specified column.

**Syntax:**

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

**Arguments Explanation:**

Argument | Required/Optional | Explanation |
---|---|---|

lookup_value |
Required |
The value which it looks for is in the leftmost column of the given table. Can be a single value or an array of values. |

table_array |
Required |
The table in which it looks for the lookup_value in the leftmost column. |

col_index_num |
Required |
The number of the column in the table from which a value is to be returned. |

[range_lookup] |
Optional |
Tells whether an exact or partial match of the lookup_value is required. 0 for an exact match, 1 for a partial match. The default is 1 (partial match). |

**Return Parameter:**

Returns the value of the same row from the specified column of the given table, where the value in the leftmost column matches the lookup_value.

**Read More: 7 Practical Examples of VLOOKUP Function in Excel**

**10 Best Practices with VLOOKUP in Excel**

We have classified the difficulty levels of the uses of the **VLOOKUP** function into three categories: **Beginner, Moderate**, and** Advanced**.

**1. Beginner Level Examples and Practices with VLOOKUP**

**i. VLOOKUP to Find Specific Data or Array Horizontally from a Table**

In the following table, a number of sales data has been recorded for the salesman. In our first example of** the VLOOKUP function**, we’ll pull out the sales record of a specified salesman.

For example, we’re going to get the sales record of **Peter **from the table.

In the output **Cell C16**, the required formula will be:

`=VLOOKUP(B16,B5:E13,{2,3,4},FALSE)`

After pressing **Enter**, you’ll get the department, month, and sales value in a horizontal array at once. In this function, we have defined the column index of three **Columns C, D, and E** in an array of **{2,3,4}**. So, the function has returned the extracted values from all those three columns.

**Read More: How to Use VLOOKUP with Two Lookup Values in Excel**

**ii. VLOOKUP Practice with Named Range in Excel**

In the first argument of the **VLOOKUP **function, we can define the array or table data with a **named range**. In the previous example, the selected array or table data range was **B5:E13**. But here we’ll name this range of data as **Sales_Data.**

To do this, we have to simply select the array and then edit the name in the **Name Box** situated on the left-top corner of the spreadsheet.

Now, the formula used in the previous example will look like this with the defined named range:

`=VLOOKUP(B16,Sales_Data,{2,3,4},FALSE)`

After pressing **Enter**, we’ll be able to extract similar data as found in the preceding section.

**Read More: How to Make VLOOKUP Case Sensitive in Excel**

**iii. Categorizing Data with VLOOKUP in Excel**

In this example, we have added an extra column named **Category **outside the data table or array. What we’ll do here is categorize the departments with **A, B, or C** based on the second table at the bottom.

**📌**** Step 1:**

➤ Select **Cell F5** and type:

`=VLOOKUP(C5,$C$16:$D$18,2,FALSE)`

➤ Press **Enter **and the function will return **A** as this alphabet denotes the **Jeans **department.

**📌**** Step 2:**

➤ Now use **Fill Handle** to autofill the entire **Column F** and you’ll be shown all the categories based on the department names.

**Read More: How to Apply Double VLOOKUP in Excel**

**2. Moderate Level Examples and Practices with VLOOKUP**

**i. Showing Error Message If Data Not Found with VLOOKUP**

Sometimes, we might be unable to find or extract the data based on our defined criterion. In that case, the **VLOOKUP **function will return an error that looks quite odd in the data table. We can replace that error message with a customized statement, such as **“Not Found”** or** “Data Unavailable”.**

For example, we’re going to find the sales record of **Robert **but this name is not available in the **Salesman **column. So, we’ll use the **IFERROR** function here and this function will define a message that will be displayed when the function is unable to match the given criterion.

In **Cell C16**, the required formula with **IFERROR **and **VLOOKUP **functions will be:

`=IFERROR(VLOOKUP(B16,Sales_Data,{2,3,4},FALSE),"Not Found")`

Now press **Enter **and you’ll find the customized statement** “Not Found”** as the function could not extract any data due to the absence of the name **‘Robert **in **Column B**.

**Related Content: How to Use Nested VLOOKUP in Excel**

**ii. VLOOKUP a Value Containing Extra Space(s)**

Our lookup value might contain a hidden space sometimes. In that case, our lookup value cannot be matched with the corresponding names present in **Column B**. So, the function will return an error as shown in the following picture.

To avoid this error message and remove space before starting to look for the specified value, we have to use the **TRIM** function inside. The TRIM function trims the unnecessary space from the lookup value.

Since **Cell B16** contains an extra space at the end of the name- Peter, the required formula to look for the name Peter only without any space in the output **Cell C16** will be:

`=VLOOKUP(TRIM(B16),B5:E13,{2,3,4},FALSE)`

After pressing **Enter**, you’ll find the extracted data for Peter.

**Related Content: How to Use Dynamic VLOOKUP in Excel**

**iii. VLOOKUP with MATCH Function in Excel**

In this section, we’ll look for two defined criteria along with columns and rows. In this two-way lookup, we have to use the **MATCH** function to define the column number from the selected array.

For example, based on the following dataset, we can draw out any kind of sales record for a specified salesman, let it be Antonio and we’ll find his department here.

In the output **Cell C18**, the required formula with the **MATCH **and **VLOOKUP **functions will be:

`=VLOOKUP(C16,B4:E13,MATCH(C17,B4:E4,0),FALSE)`

Press **Enter **and the formula will return **‘Jeans’** as **Antonio **works in the jeans department.

You can change the output criteria in **Cell C17** and other corresponding sales records will show up instantly. You can also alter the name of the salesman in **Cell C16** to find sales data for other salesmen as well.

**Similar Readings:**

**How to Use VLOOKUP for Multiple Columns in Excel****VLOOKUP from Multiple Columns with Only One Return in Excel****How to Use VLOOKUP to Return Multiple Columns in Excel****How to Use VLOOKUP to Find Duplicates in Two Columns****How to Use VLOOKUP Function with Exact Match in Excel****How to Find Second Match with VLOOKUP in Excel****How to Vlookup with Multiple Matches in Excel****VLOOKUP and Return All Matches in Excel**

**iv. Pulling Out Data Based on Partial Match with VLOOKUP**

**VLOOKUP **function also works with the use of **wildcard characters** through which we can look for a **partial match** in the table and pull out the corresponding data.

For example, we can look for the actual name with a partial text **“ton”** only and then we’ll extract the sales record for that salesman.

The required formula in **Cell C16** should be:

`=VLOOKUP("*ton*",B5:E13,{2,3,4},FALSE)`

After pressing **Enter**, the formula will return the sales data for **Antonio **as this name contains the specified text-** “ton”**.

**v. Extracting the Last Value in a List with VLOOKUP**

Extracting the last or final value in a long range of cells is too simple with the **VLOOKUP **function.

In the following picture, **Column B** contains numbers with random values. We’ll extract the last value from this column or the range of cells **B5:B14**.

The necessary formula with the **VLOOKUP **function in the output **Cell D8** will be:

`=VLOOKUP(9.99999999999999E+307,$B$5:$B$14,TRUE)`

Press **Enter **and you’ll get the value present in the last cell in that column.

**🔎**** How Does the Formula Work?**

- In this function, the lookup value is a huge number that has to be searched in the range of cells
**B5:B14**. - The lookup criteria here in the third argument is
**TRUE**which denotes the approximate match of that number. - The
**VLOOKUP**function looks for this huge value and returns the last value based on the approximate match as the function is unable to find the defined number in the column.

**Similar Readings:**

**How to Use the VLOOKUP Function to Compare Two Lists in Excel****Excel VLOOKUP to Find the Last Value in the Column****Return the Highest Value Using the VLOOKUP Function in Excel****How to Use the VLOOKUP Ascending Order in Excel****VLOOKUP Fuzzy Match in Excel****VLOOKUP with Drop Down List in Excel****How to Use VLOOKUP to Search Text in Excel****VLOOKUP with Numbers in Excel****How to Use VLOOKUP for Rows in Excel****How to Use Column Index Number Effectively in Excel VLOOKUP****How to Apply VLOOKUP by Date in Excel****VLOOKUP from Another Sheet in Excel**

**3. Advanced Level Examples and Practices with VLOOKUP**

**i. VLOOKUP to Find Case-Sensitive Text in Excel**

Sometimes, we may have to look for the case-sensitive matches and then extract the data. In the following table, **Column B** has been modified a little and if you notice, this column has now the name **‘Simon’** thrice but each of them with different cases.

We’ll look for the exact name **‘SIMON’** and draw out the sales data based on the match.

The required formula in the output **Cell C16** will be:

`=VLOOKUP(TRUE, CHOOSE({1,2,3,4}, EXACT(B16, B5:B13), C5:C13,D5:D13,E5:E13), {2,3,4}, FALSE)`

After pressing **Enter **you’ll be displayed the corresponding sales data for the exact name **‘SIMON’** only.

**🔎**** How Does the Formula Work?**

- The lookup array of the
**VLOOKUP**function has been defined with the combination of**CHOOSE**and**EXACT**functions. - The
**EXACT**function here looks for the case-sensitive matches of the name SIMON in the range of cells**B5:B13**and returns an array of:

**{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE}**

**CHOOSE**function here extracts the entire table data but only the first column shows the boolean values**(TRUE and FALSE)**instead of the salesman names.**VLOOKUP**function looks for the specified boolean value**TRUE**in that extracted data and subsequently returns the available sales records based on the row number of the matched lookup value**TRUE**.

**Similar Readings:**

**How to Use VLOOKUP Formula in Excel with Multiple Sheets****VLOOKUP Example Between Two Sheets in Excel****How to VLOOKUP and SUM Across Multiple Sheets in Excel****IF and VLOOKUP Nested Function in Excel****How to Use IF ISNA Function with VLOOKUP in Excel****Combining SUMPRODUCT and VLOOKUP Functions in Excel****How to Use VLOOKUP with COUNTIF****How to Combine SUMIF and VLOOKUP in Excel****Use VLOOKUP to Sum Multiple Rows in Excel****INDEX MATCH vs VLOOKUP Function**

**ii. Use of Drop-Down List Items as VLOOKUP Values**

Instead of altering the name or other criteria manually, we can also create **drop-down lists** for the defined criteria and extract data. In the following table, the sales values of a number of salesmen have been recorded for three different months. Under the primary table, we’ll create two drop-downs for the salesmen and months.

**📌**** Step 1:**

➤ Select **Cell C15** where the drop-down list will be assigned.

➤ From the **Data **ribbon, choose **Data Validation** from the **Data Tools **drop-down.

A dialogue box will appear.

**📌**** Step 2:**

➤ In the **Allow **box, select the option **List**.

➤ In the **Source **box, specify the range of cells containing the names of all salesmen.

➤ Press **OK **and you’re done with the making of the first drop-down.

Like in the picture below, you’ll find a drop-down list for all salesmen.

Similarly, you have to create another drop-down list for the range of cells** (C4:E4)** containing the names of the months.

**📌**** Step 3:**

➤ Now select the name **Antonio **from the **Salesman **drop-down.

➤ Select the month name **Feb **from the **Month **drop-down.

➤ Finally, in the output **Cell C17**, the corresponding formula will be:

`=VLOOKUP(C15,B5:E13,MATCH(C16,B4:E4,0),FALSE)`

➤ Press **Enter **and you’ll be shown the resultant data right away.

Now you can alter any of the names or months from the **Salesman **and **Month **drop-downs and find the corresponding outputs in **Cell C17** immediately.

**💡**** Things to Keep in Mind**

- The
**lookup_value**can be a single value or an array of values. If you enter an array of values, the function will look for each of the values in the leftmost column and return the same row’s values from the specified column. - The function will look for an approximate match if the
**[range_lookup]**argument is set to 1. In that case, it will always look for the lower nearest value of the**lookup_value**, not the upper nearest one. - If the
**col_index_number**is a fraction in place of an integer, Excel itself will convert it into the lower integer. But it will raise**#VALUE!**error if the**col_index_number**is zero or negative.

**Download Practice Workbook**

You can download the Excel workbook that we’ve used to prepare this article.

**Concluding Words**

I hope all the uses of the **VLOOKUP **function in this article will now help you to apply them in your Excel spreadsheets while extracting data based on a lookup value. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.

## Related Articles

**Excel LOOKUP vs VLOOKUP****XLOOKUP vs VLOOKUP in Excel****How to Use VLOOKUP Function with INDIRECT Function in Excel****Perform VLOOKUP by Using Column Index Number from Another Sheet****How to Find Column Index Number in Excel VLOOKUP****How to Remove Vlookup Formula in Excel****How to Apply VLOOKUP to Return Blank Instead of 0 or NA****How to Hide VLOOKUP Source Data in Excel****How to Copy VLOOKUP Formula in Excel****How to Use VLOOKUP in VBA in Excel****Use Excel VBA VLOOKUP to Find Values in Another Worksheet****How to Use VLOOKUP Function in Excel VBA****Vlookup Multiple Values****How to Use VLOOKUP for Partial Match in Excel****How to Use IFERROR with VLOOKUP in Excel****Excel VLOOKUP Function with IF Condition**

Hi,

I did not get the department, month, and sales value in a horizontal array at once with the formula =VLOOKUP(B16;B5:E13;{2\3\4};FALSE) as {2\3\4} doesnt work. What version of Excel did you use when ve created this tutorial? Excel 2019 ?

Hi, Marian. All our Excel-related contents on this website have been made using Excel 365. I suggest you try the formulas in Excel 365 or Excel 2021 version. Thanks!