How to Use VLOOKUP in Microsoft Excel

How to Use VLOOKUP in Microsoft Excel

I came to find out that a lot of people are having serious issues understanding what VLOOKUP is not even knowing how to use it. So, I decided to write an article to solve ethe puzzle.

I hope this helps?

VLOOKUP is a useful function in Microsoft Excel that allows you to look up a value in a table and return a corresponding value from another column. For example, you can use VLOOKUP to find the price of a product by its name, or the name of an employee by their ID number. In this article, we will explain how to use VLOOKUP in Microsoft Excel with some examples and tips.

What is the syntax of VLOOKUP?

The syntax of VLOOKUP is as follows:

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

The arguments of VLOOKUP are:

lookup_value: The value you want to look up. This can be a cell reference or a constant value.

table_array: The range of cells that contains the lookup table. The first column of this range must contain the lookup values.

col_index_num: The column number in the table_array that contains the return values. The first column is 1, the second column is 2, and so on.

range_lookup: An optional argument that specifies whether you want an exact match or an approximate match. If you omit this argument or set it to TRUE, VLOOKUP will return an approximate match. If you set it to FALSE, VLOOKUP will return an exact match.

How to use VLOOKUP for an exact match?

An exact match means that VLOOKUP will only return a value if it finds an exact match for the lookup_value in the first column of the table_array. If there is no exact match, VLOOKUP will return an #N/A error.

To use VLOOKUP for an exact match, you need to set the range_lookup argument to FALSE. For example, suppose you have a table of products and prices in cells A2:B10, and you want to look up the price of a product by its name in cell D2. You can use the following formula in cell E2:

=VLOOKUP(D2,A2:B10,2,FALSE)

This formula will look up the value in D2 (the product name) in the first column of A2:B10 (the product table), and return the value from the second column of A2:B10 (the price column). If the product name in D2 is not found in A2:B10, the formula will return #N/A.

How to use VLOOKUP for an approximate match?

An approximate match means that VLOOKUP will return the closest value that is less than or equal to the lookup_value in the first column of the table_array. If there is no value that is less than or equal to the lookup_value, VLOOKUP will return an #N/A error.

To use VLOOKUP for an approximate match, you can omit the range_lookup argument or set it to TRUE. However, you need to make sure that the first column of the table_array is sorted in ascending order. For example, suppose you have a table of tax rates and income brackets in cells A2:B6, and you want to look up the tax rate for a given income in cell D2. You can use the following formula in cell E2:

=VLOOKUP(D2,A2:B6,2)

This formula will look up the value in D2 (the income) in the first column of A2:B6 (the income bracket table), and return the value from the second column of A2:B6 (the tax rate column). If the income in D2 is not within any of the income brackets in A2:B6, the formula will return #N/A.

What are some tips and tricks for using VLOOKUP?

Here are some tips and tricks for using VLOOKUP effectively:

Use absolute references for the table_array argument to prevent it from changing when you copy or move the formula. For example, instead of using A2:B10 as the table_array, use $A$2:$B$10.

Use named ranges or tables for the table_array argument to make your formulas more readable and easier to manage. For example, instead of using $A$2:$B$10 as the table_array, you can name it ProductTable and use it as ProductTable.

Use wildcards (* and ?) for partial matches when you set the range_lookup argument to FALSE. For example, if you want to look up a product name that contains “pen”, you can use =VLOOKUP(“pen”,ProductTable,2,FALSE).

Use IFERROR function to handle errors returned by VLOOKUP. For example, if you want to display “Not found” instead of #N/A when there is no match, you can use =IFERROR(VLOOKUP(D2,A2:B10,2,FALSE),“Not found”).

Use other functions with VLOOKUP to perform more complex tasks. For example, if you want to look up a value based on multiple criteria, you can use VLOOKUP with MATCH, INDEX, or XLOOKUP functions.

Conclusion

VLOOKUP is a powerful and versatile function in Microsoft Excel that can help you find and retrieve data from a table. By understanding the syntax and arguments of VLOOKUP, and applying some tips and tricks, you can use VLOOKUP for various purposes and scenarios. Happy VLOOKUPing!