**Syntax of VLOOKUP Formula**

**Example of VLOOKUP Formula**

**Possible Errors returned by the VLOOKUP Formula**

**VLOOKUP formula** matches a string against the 1st column of a range and returns any cell value from the matched row.

## VLOOKUP Formula Syntax

VLOOKUP Formula has three parts:

**VLOOKUP (value_to_find, range_to_search_in, column_number_to_return, match_type)**

### value_to_find

*value_to_find* is the value that we would like to find. You can specify a string, a number or a cell address as the value you would like to search for. While using a string enclose it within quotes (Ex. "Apple"). When searching for a cell provide the address of the cell (Ex. B12). Numbers can be entered as such. (Ex. 19)

### range_to_search_in

The *range_to_search_in* is the range in which we would like to search for the value and return the match. When you specify this range, the VLOOKUP only searches the **first** column of the range (and NOT all the columns) for the value which we would like to find.

### match_type

*match_type* is specified as either TRUE or FALSE. If this value is specified as FALSE (or false in small-case), the VLOOKUP function will find and exact match. If it does not find a match, it will return an error value. However if this value is specified as TRUE, then the VLOOKUP function tries to find and exact match but if it does not, the next largest value that is less than lookup_value is returned. This parameter is optional and if not specified is taken a TRUE by default. (To make this work properly, the values in the column that we want to return from the function have to in an ascending order – but you can ignore that for now. For most cases, a match_type value of FALSE is all that we will ever need.)

## Example of a VLOOKUP Formula

Let’s look at an example of the VLOOKUP formula. Suppose we had a table as shown in the above example. We have a list of products in the first column and their sales values in the second. Suppose you wanted to know how much were the sales for the product “Software”. We can write the VLOOKUP formula as:

**=VLOOKUP(A13,A2:B9,2,FALSE)** or

**=VLOOKUP(“Software”,A2:B9,2,FALSE)**

Both of the above formulas would return a value of 10. How does it work ? Let’s look at each part of the formula carefully. The first part of the formula, as we saw earlier, is the value that we would like to find. In our case, we can either specify the cell (A13) or the string (“Software”) that we would like to find. The second part is the range in which we would like to search which in our case is A2:B9 (the table in which we have stored the values). The third part is the column that we would like to return a value from in case the value we were trying to find was found in a particular row. Specifying a value of 1 would return the first column (which was also the column we searched against). A column value of 2 would return the figures from the second column (which is what we want in our example).

### How to enter the VLOOKUP formula in an Excel Sheet

1. Select the cell in which you want to place the formula

2. Type the formula as =VLOOKUP(

3. Move the cursor using the up-down or left-right arrow keys and take it to the cell which contains the value that you are trying to find.

4. Press the comma key (,)

5. Again move the cursor using the up-down or left-right arrow keys and take it to the first cell (top-left) of the range that you would like to search.

6. Now keeping the SHIFT key pressed, move the cursor again and take it to the last cell (bottom-right) of the range that you would like to search.

7. Press the comma key (,) again

8. Type in the column number from which you would like to return the value from. (If you don’t get it at first, simply type in 1).

9. Press the comma key (,) again

10. Type in FALSE and then close the formula bracket by typing in ).

(Check out the clip above for knowing if the values you’ve entered are in the same order. In the end your formula should look something like this **=VLOOKUP(“Software”,A2:B9,2,FALSE)** )

## Possible Errors with the VLOOKUP Formula

The VLOOKUP formula can result in the following error values:

### VLOOKUP #N/A Error

The #N/A Error value in VLOOKUP is one of the most frequently occurring error value. This signifies that the value that you are trying to find does not exist in the range in which you are trying to find it. If you get this error, try going back and check whether the value that you are trying to find exists and is in the first column of the range in which you are trying to find it. Carefully check the range that you’ve specified. Foe example, if we were to specify =VLOOKUP(“Cake”,A2:B9,2,FALSE) in the above example, it would result in an #N/A error value simply because the value “Cake” does not exist in the first column of range in which we are trying to find it.

### VLOOKUP #REF! Error

#REF! error in VLOOKUP specifies that you have specified a match to be returned from a column that does not exist in the range in which you are trying to find the value. For example if we were to write the formula as =VLOOKUP(“Software”,A2:B9,33,FALSE), it would give us the #REF! value because we have specified that if there’s is a match, it should return the 33rd column from the range. However the range that we have specified (A2:B9) has only two columns (A and B) and hence trying to reference the 33rd column results in an error.

### VLOOKUP #NAME? Error

#NAME? Error in VLOOKUP can result from wrongly specifying address the range in which to find the value. Say for example you wanted to write =VLOOKUP(“software”,A1:B9,2,FALSE) but instead erroneously entered =VLOOKUP(“software”,A1:BBB9,2,FALSE) (two extra B’s). Now the cell BBB9 does not exist anywhere in the sheet and as a result the VLOOKUP function throws up the #NAME? error value.

You can know more about **hiding errors in a worksheet** here.

You can download an **example of VLOOKUP formula** here or click on the button below:

Hi, thanks for explanation! This tutorial also helped me much to understand how vlookup works:

http://www.myhowtoos.com/en/excel-howtoos/84-how-to-match-values-in-excel-using-vlookup

July 10th, 2009 at 9:09 amI wonder how to generate the formula from excel to other excel? Please email the answer to my mail above. Thank

August 14th, 2009 at 4:09 amboomblue,

All you need to do a vlookup across sheets is to ensure that in step 5, you select the range from the other workbook rather than the same workbook. All the other steps would remain essentially the same in your vlookup formula.

Here’s an example:

=VLOOKUP(A1,'[Demo.xls]MySheet1′!$A$1:$B$10,2,FALSE)where you are trying to lookup cell A1 in the present sheet with range A1 to B10 in the worksheet titled Demo.xls and in the worksheet named MySheet1. The vlookup syntax remains the same with only the range_to_search_in changing being referred to from another workbook.regards,

August 16th, 2009 at 9:53 pmHi, thanks for explanation! This tutorial also helped me much to understand how vlookup works

Thanxxxxxxxxxxxx

September 26th, 2009 at 6:06 amHi Sanjay,

Welcome to da TaB and glad the tutorial was of use.

Regards,

September 26th, 2009 at 9:16 ami want know about the vlookup formulas and how to use the formulas?

February 1st, 2011 at 1:18 amwhat is the benefit of the

May 22nd, 2011 at 11:53 amvlookup,

hlookup

,pivottable in excel

sir i want to known excle formula

September 23rd, 2011 at 2:02 amso plZ send some kit for this

i need to know all about , ms excel 2007 Vlookup how to and where yo use, all criteria.

July 9th, 2012 at 1:47 pmWhen using a vlookup and you want the #N/A to show as a zero value instead. How can I get excel to process.

October 8th, 2012 at 11:46 amthank alot for explane vlookup

December 4th, 2012 at 2:33 ami love the details especially steps in explainations

December 27th, 2012 at 7:59 pmSimply Superbb…… (Its BEST SITE) —- GR8 Works…….. Thanks to 1 & All USERS

February 13th, 2013 at 7:44 amRealy its use ful for me

August 23rd, 2013 at 10:54 amthanks.

Help when show pratical vedio on with tutorial…..Bhagwat Tambe

December 18th, 2013 at 10:53 amPlease give me following Quation Formula…

December 18th, 2013 at 11:00 amTow Sheet :-Sheet 1 & Sheet2

Sheet 1 :- Date & Transction No & Amount

Sheet 1:- Date & Transction No & Amount ……I want Vlookup where i Match date & transction no against Catch get the amount

Bhagwat Tambe..9702046255…..

excellent

December 22nd, 2013 at 6:55 pm