The basic of vlookup

vlookup – Something you should know about

The basic of vlookup

Definition of LOOKUP (http://www.merriam-webster.com/dictionary/lookup)

:  the process or an instance of looking something up;especially:  the process of matching by computer the words of a text with material stored in memory

v, in the vlookup, means vertical.  Together it means looking something up vertically!

so… What does vlookup do?

The following screenshot shows you the top 10 box offices in 2013.  If you want to know which movie ranks 1st, 2nd, or 5th, and their corresponding information, vlookup is the formula you need.

Image

In the following example, G3 is set as the variable (lookup_value), i.e. “Rank”.  The rest of the information from “Movie Title” to “Open” are retrieved by using vlookup

Excel TIps 25.2

(for a table with only 10 records, we may do the lookup by eyeballing quickly.  A small table is used here for demonstration purpose.  In reality, we are more likely to deal with a table with hundreds or thousands of records where eyeballing would no longer be an option)

The Syntax

Image

  • lookup_value = the input variable, i.e. what to look up?  ==> G3
  • table_array = the table holding the data, i.e. where to look at? ==> A2:E12
  • col_index_num = the column to return when a Match is found
  • 1 for “Rank”; 2 for “Movie Title”; 3 for “Studio” etc.
  • [range_lookup] = [optional];
  • TRUE means approximate match; FALSE means exact match.  When omitted, it means TRUE

To put everything together, vlookup(lookup_value,table_array,col_index_num,[range_lookup]) means

vertically lookup the value i want (i.e. the rank in G3), from the Table of the data source (i.e. A2:E12), then return the value of the Xth Column in the table as specified by the Col_Index_Num (2 for second column, i.e. Movie Title; 3 for 3rd column, i.e. Studio; etc), for an exact match is found (FALSE)

With the vlookup formula constructed correctly:

  • =VLOOKUP(G3,A2:E12,2,FALSE) for “Movie Title”
  • =VLOOKUP(G3,A2:E12,3,FALSE) for “Studio”
  • =VLOOKUP(G3,A2:E12,4,FALSE) for “Gross”
  • =VLOOKUP(G3,A2:E12,5,FALSE) for “Open”

you will get the following different results for different variable (rank) input:

Image

Image

Image

As there is no 22 in the Table_array (the data source), and we request an exact match (FALSE in the last argument), it returns “#N/A” as it cannot lookup 22 in the Table.

Important Notes about using vlookup:

1) VLOOKUP ALWAYS LOOK FOR VALUE AT THE LEFTMOST COLUMN OF THE TABLE_ARRAY, AND ALWAYS IN THE DIRECTION FROM LEFT TO RIGHT 

  • If you look up “Rank“, the Table_Array must start with column A so that the Rank is in the LEFTMOST column of the Table_Array;
  • If you look up “Movie Title“, the Table_Array must start with column B so that it is in the LEFTMOST column of the Table_Array; and so on…
  • If you look up “Movie Title”, the Table_Array now becomes B2:E12 and Col_Index_Num will be shifted by 1 (to the right) accordingly.  Col_Index_Num always count from the LEFTMOST column of the Table_Array.  Now 2 refers to “Studio”; 3 refers to “Gross”; 4 refers to “Open”; 5 refers to “#N/A” as there are only 4 columns in the Table_Array (column B:E)
  • The col_index_num must be between 1 and the width of the table_array

2) VLOOKUP WILL STOP LOOKING UP ONCE IT FINDS THE 1ST MATCH, AND RETURN THE CORRESPONDING VALUE FOR THE 1ST MATCH

  • If the lookup_value in the Table_Array is not unique, you will get the result for the 1st value in the Table_Array.  Since vlookup starts from top to bottom, the 1st match means the one on the highest position.
  • E.g. if there are two movies (say IronMan and Despicable Me) having the same rank say 1, and your lookup value is 1.  Then you will get the result of IronMan as it is the first match in the table.

RULE OF THUMB FOR BASIC USER: 

3) ALWAYS USE FALSE FOR AN EXACT MATCH TO AVOID UNEXPECTED RESULT.

When FALSE is used, you don’t have to worry about the sorting order of the lookup value in the Table_Array.

When TRUE is used, the lookup value in the Table_Array must be in sorted in ascending order.

Other topics about vlookup:

  1. The basic of vlookup
  2. Tips in constructing vlookup
  3. vlookup with Match 
  4. vlookup options – True or False?
  5. Advanced vlookup – Text vs. Number
  6. Advanced vlookup – Wildcard Characters “?” and “*”
  7. Alternative to vlookup – Index and Match
  8. Three different ways to do case-sensitive lookup

Advertisements
This entry was posted in Formula and tagged . Bookmark the permalink.

9 Responses to The basic of vlookup

  1. Sidney says:

    hi, how do i search for a specific combination of characters exclusively i.e. if one cell contains 5A;3B&4C while another cell contains 5AA;3B&4C, and my search criteria is 5A, i want the results to return only the cell that contains 5A, not both cells because i am not looking for 5AA

    Like

  2. Pingback: Vlookup

  3. Pingback: Alternative to vlookup – Index and Match | wmfexcel

  4. Pingback: vlookup – Text vs. Number | wmfexcel

  5. Pingback: vlookup – True or False?? | wmfexcel

  6. Pingback: vlookup with Match | wmfexcel

  7. Pingback: vlookup – Something you should know about | wmfexcel

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s