Your SEO optimized title page contents

How to use VLOOKUP

How to use the VLOOKUP

The VLOOKUP function performs a vertical lookup by searching for a value in the first column of a table and returning the value in the same row in the index_number position. The VLOOKUP function is a built-in function in Excel that is categorized as a Lookup/Reference Function.

The VLOOKUP function can be entered as part of a formula in a cell of a worksheet.

Syntax

The syntax for the VLOOKUP function in Microsoft Excel is:

VLOOKUP( value, table, index_number, [approximate_match] )

Parameters or Arguments
value
The value to search for in the first column of the table.
table
Two or more columns of data that is sorted in ascending order.
index_number
The column number in table from which the matching value must be returned. The first column is 1.
approximate_match
Optional. Enter FALSE to find an exact match. Enter TRUE to find an approximate match. If this parameter is omitted, TRUE is the default.

Returns
The VLOOKUP function returns any datatype such as a string, numeric, date, etc.
If you specify FALSE for the approximate_match parameter and no exact match is found, then the VLOOKUP function will return #N/A.
If you specify TRUE for the approximate_match parameter and no exact match is found, then the next smaller value is returned.
If index_number is less than 1, the VLOOKUP function will return #VALUE!.
If index_number is greater than the number of columns in table, the VLOOKUP function will return #REF!

Example:

lookup_value is the value to search for.
This can be either a value (number, date or text) or a cell reference (reference to a cell containing a lookup value), or the value returned by some other Excel function. For example:

Look up for number: =VLOOKUP(40, A2:B15, 2) – the formula will search for the number 40.
Look up for text: =VLOOKUP(“apples”, A2:B15, 2) – the formula will search for the text “apples”. Please pay attention that you always enclose text values in “double quotes”.
Look up for value in another cell: =VLOOKUP(C2, A2:B15, 2) – the formula will search for the value in cell C2.
table_array is two or more columns of data.
The VLOOKUP function always searches for the lookup value in the first column of table_array. Your table array may contain various values such as text, dates, numbers, or logical values. Values are case-insensitive, meaning that uppercase and lowercase text are treated as identical.

So, our formula =VLOOKUP(40, A2:B15,2) will search for “40” in cells A2 to A15 because A is the first column of table_array. Hopefully, everything is clear so far : )

col_index_num is the column number from which the value should be returned.
The left-most column in the specified table_array is 1, the second column is 2, the third column is 3, and so on.

Well, now you can read the entire formula =VLOOKUP(40, A2:B15,2). It searches for “40” in cells A2 through A15 and returns a value from column B in the same row (because B is the 2nd column in the specified table array).

How to do vlookup in Excel from another worksheet?

To do Vlookup from a different Excel sheet, you should enter the worksheet’s name and an exclamation mark in the table_array argument before the range of cells, e.g. =VLOOKUP(40, Sheet2!A2:B15,2). The formula indicates that the lookup range A2:B15 is located in Sheet2.

Of course, you don’t have to type the sheet’s name manually. Simply, start typing the formula and when it comes to the table_array argument, switch to the lookup worksheet and select the range using a mouse.

The formula you see in the screenshot below searches for the text “Product 1” in column A in the “Prices” worksheet:

=VLOOKUP(“Product 1”,Prices!$A$2:$B$9,2,FALSE)

 

Learn excel from us. A step by step tutorials to make you up-skill from beginner to pro. You can visit our main page to know more.

 

Once you close the workbook with your lookup table, your VLOOKUP formula will work anyway, but it will display the full path for the lookup workbook, as shown below:

How to use wildcard characters in VLOOKUP formula

Like in many other formulas, you can use the following wildcard characters with Excel VLOOKUP function:

Question mark (?) to match any single character, and
Asterisk (*) to match any sequence of characters.
Using wildcard chars may prove really useful in many cases:

When you do not remember the exact text you are looking for.
When you want to find some word that is part of the cell’s contents. Usually, the VLOOKUP function searches by the entire content of a cell, as if you selected the option “Match entire cell content” in the standard Excel Find dialog.
When a lookup column contains extra leading or trailing spaces. If it is the case, you may rack your brain trying to figure out why the normal formula does not work.
Example 1. Look up text starting or ending with certain characters
Suppose, you want to find a certain customer in the below database. You cannot remember his surname, but you know it starts with “ack”. So, you do a vlookup in this way:

=VLOOKUP(“ack*”,$A$2:$C$11,1,FALSE)

Once you are sure you’ve found the correct name, you can use a similar formula to get the sum paid by that customer. You only have to change the 3rd parameter in the formula to the appropriate column index, 3 in our case:

=VLOOKUP(“ack*”,$A$2:$C$11,3,FALSE)

Here a few more examples of VLOOKUP formulas with wildcard characters:

=VLOOKUP(“*man”,$A$2:$C$11,1,FALSE) – find the name ending with “man”.

=VLOOKUP(“ad*son”,$A$2:$C$11,1,FALSE) – find the name starting with “ad” and ending with “son”.

=VLOOKUP(“?????”,$A$2:$C$11,1,FALSE) – find a 5-character last name.