VLOOKUP Formula Excel – How to use Excel VLOOKUP Function




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)

vlookup-example-and-syntax

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

vlookup

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:

download example of VLOOKUP function


Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?


XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>


Comments and Trackbacks

  1. Dawood Mamedoff wrote:

    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

  2. boomblue wrote:

    I wonder how to generate the formula from excel to other excel? Please email the answer to my mail above. Thank

  3. Ajay wrote:

    boomblue,

    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,

  4. Sanjay wrote:

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

    Thanxxxxxxxxxxxx

  5. Ajay wrote:

    Hi Sanjay,

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

    Regards,

  6. Rushi Reddy wrote:

    i want know about the vlookup formulas and how to use the formulas?

  7. apparao wrote:

    what is the benefit of the
    vlookup,
    hlookup
    ,pivottable in excel

  8. excle formula software or sheet wrote:

    sir i want to known excle formula
    so plZ send some kit for this

  9. hanif wrote:

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

  10. Rachel wrote:

    When using a vlookup and you want the #N/A to show as a zero value instead. How can I get excel to process.

  11. Lamitha wrote:

    thank alot for explane vlookup

  12. april wrote:

    i love the details especially steps in explainations

  13. SANJAY DESHMUKH wrote:

    Simply Superbb…… (Its BEST SITE) —- GR8 Works…….. Thanks to 1 & All USERS

  14. Jitendra wrote:

    Realy its use ful for me
    thanks.

  15. Bhagwat Tambe wrote:

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

  16. Bhagwat Tambe wrote:

    Please give me following Quation Formula…
    Tow 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…..

  17. Abdleghani wrote:

  1. ADDRESS Formula Excel - How to use Excel ADDRESS Function | da TaB is On wrote:

    [...] Syntax of ADDRESS Formula Example of ADDRESS Formula Possible Errors returned by the ADDRESS Formula [...]

Subscribe

Keep up with the latest stories - Delivered right to your inbox
feedburner

Translate

English flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagDutch flagHindi flagPolish flagSwedish flagFilipino flagHebrew flagIndonesian flagUkrainian flagThai flagTurkish flag
treeemap software for excel