VLOOKUP with Easy-to-Understand Examples

VLOOKUP is an important function of Microsoft Excel that permits you to explore a value in a table and obtain the corresponding information from different parts of the same table. Most often, it is used in carrying out activities like data analysis, lookup, and referencing. Hence, let us now simplify this idea by giving examples that can be easily understood.

Basic VLOOKUP

Suppose you have a table of student grades like this:

You want to find the grade of student Bob. Here’s a simple VLOOKUP formula for that:

=VLOOKUP(102, A2:C4, 3, FALSE)

This formula consists of four arguments:

• 102: The value to search for (Bob’s Student ID).
• A2:C4: The table range where Excel should search for the value.
• 3: The column index from which to retrieve the result (Grade is in the third column).
• FALSE: This signifies an exact match.

The result will be:

Execution:

• Through the use of Excel, I will look for ‘102’ in the first column (A2:A4).
• It is found in the second row.
• This formula retrieves and displays grade ‘B’, which is in the third column.

Range Names

Assuming your table is named “StudentTable,” the formula becomes more readable:

=VLOOKUP(102, StudentTable, 3, FALSE)

This is functionally the same as the first example but uses a named range (“StudentTable”) instead of specifying the cell range directly.      

Approximate Match

Now, let’s consider a table of salary ranges:

You want to find the job title for an employee with a salary of $70,000.

VLOOKUP Formula for Approximate Match:

=VLOOKUP(70000, A2:C4, 3, TRUE)

Note the change to the fourth argument (TRUE), allowing for an approximate match.

Execution:

Excel looks for the value “70000” in the first column (A2:A4).

It finds that $70,000 falls in the second row (between 50001 and 80000).

The formula returns the corresponding job title “Associate.”

Handling Errors

In case this cannot find a match, it returns an error. You can use IFERROR to handle this situation:

=IFERROR(VLOOKUP(104, A2:C4, 3, FALSE), “Not Found”)

IFERROR Formula:

VLOOKUP(104, A2:C4, 3, FALSE): This function as in Example 1.

IFERROR(…, “Not Found”): If its results in an error, it returns “Not Found” instead.

Execution:

Since Student ID 104 is not in the table, its returns an error.

IFERROR catches the error and displays “Not Found” instead.