How to use VLOOKUP in Excel—Download 5 practice sheets for VLOOKUP.

After reading this article, you will be able to easily use the VLOOKUP formula to retrieve data from another table. We also provide 5 free downloadable Excel practice workbooks so you can master VLOOKUP with real-world datasets.

What is VLOOKUP in Excel?

In Excel, the V in VLOOKUP stands for Vertical. This formula allows you to look up a search value vertically (down a column) in a table range (table array) and retrieve a corresponding value from another column in the same row. VLOOKUP supports both exact and approximate matches. Note that the lookup value must always reside in the first (leftmost) column of your selected table array.

Why Do We Need the VLOOKUP Formula?

• Instant Data Retrieval: Pull matched data across massive datasets in seconds.
• Time-Saving Automation: Eliminates manual searching, copying, and pasting, which reduces the chance of human error.
• Clean Reporting: Displays the matched value in the exact cell where you need it, keeping your spreadsheet clean and automated.

Key Features of VLOOKUP

1. Left-to-Right Search: VLOOKUP only searches from left to right. The lookup value must be in the first column of the selection.
2. Flexible Matching: Supports both exact matching (e.g., finding a specific ID) and approximate matching (e.g., sorting scores into ranges).
3. First Match Priority: If duplicate lookup values exist, VLOOKUP returns the first match it finds.
4. Binary Input Support: You can use 1 instead of TRUE for approximate matches, and 0 instead of FALSE for exact matches.
5. Column-Based Retrieval: Finds and returns data based on a column index number that you specify.

How to Use VLOOKUP in Excel (Formula Syntax)

Explanation of VLOOKUP Formula Arguments

Here is the standard syntax of the VLOOKUP formula:

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

Let’s explain each of these arguments:

1. lookup_value: The search value you want to look for. This value must be located in the first (leftmost) column of your table array.

2. table_array: The range of cells (the table or data range) that contains both the lookup column and the target data you want to retrieve.

3. col_index_num: The Column Index Number. This is the column number in the selected table array from which you want to retrieve the value (starting with 1 for the leftmost column).

4. range_lookup: The type of search match you want:

• FALSE (or 0): Exact Match. Looks for an exact match (highly recommended for IDs, names, etc.).
• TRUE (or 1): Approximate Match. Looks for the closest match less than or equal to the lookup value (used for grading scales, tax brackets, etc.).

If you want to retrieve the exact match value, write “false”, and if you want an approximate match, then write “true”.

Step-by-Step Example: Using VLOOKUP in Excel

In this example, we want to look up each employee’s Appraisal Score in a reference table and retrieve their corresponding % of Increment.

The Solution:

We will use the VLOOKUP formula to retrieve the predetermined percentage of increments according to the appraisal score of each employee.

Step 1: Enter the Formula

Select cell F3 (where you want to retrieve the increment percentage) and type =VLOOKUP( to start the formula.

Step 2: Select the Lookup Value

Select cell E3 (the employee’s Appraisal Score) as the value we want to search for, and add a comma.

=VLOOKUP(E3,

Step 3: Select the Table Array

Select the reference table range (columns A and B containing scores and increment values) and add a comma.

Step 4: Lock the Table Array (Absolute References)

To lock the range so it doesn’t shift when we copy the formula down, press F4 to add dollar signs ($) to the range selection:

=VLOOKUP(E3, $A$3:$B$6,

Tip: Locking the table array prevents Excel from updating the reference range when copying the formula down to other cells.

Step 5: Specify the Column Index Number

Specify the column number from which to retrieve the value. In our selected range, the increment percentage is in column 2. Add a comma.

=VLOOKUP(E3, $A$3:$B$6, 2,

Step 6: Define Range Lookup and Close Parentheses

Enter FALSE (or 0) for an exact match. Close the parentheses ) and press Enter.

=VLOOKUP(E3, $A$3:$B$6, 2, FALSE)

Excel returns the correct increment value for cell F3!

Step 7: Copy the Formula Down

Double-click the bottom-right corner of cell F3 (the fill handle) or drag it down to retrieve increments for the rest of the employees.

Download 5 Free VLOOKUP Practice Sheets

Practice is the key to mastering Excel. To help you build confidence, we have prepared 5 free downloadable VLOOKUP practice sheets with real-world examples. Download them below and start practicing right away!

Download the 1st Practice workbook for VLOOKUP Formula:

Download the 2nd practice workbook for the VLOOKUP formula:

Download the 3rd practice workbook for the VLOOKUP formula:

Download the 4th practice workbook for the VLOOKUP formula:

Download the 5th practice workbook for the VLOOKUP formula:

Thanks for reading!

Please share this post and leave your valuable feedback in the comment box below.

To buy Microsoft Excel, Click Here.

Also, check out our other detailed Excel function tutorials:

• HLOOKUP in Excel – Explanation with Example
• IF Function in Excel – Explanation with Example
• INDEX Function in Excel – Download Practice Sheet
• MATCH Function in Excel – Download Practice Sheet
• SUMIF Function in Excel – Download Practice Sheet
• COUNTIF Function in Excel – Download Practice Sheet