The basic of vlookup

January 27, 2020 Excel tips,

The basic of vlookup

by MF

vlookup – Something you should know about

: 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.

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

(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

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:

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.

#evba #etipfree #kingexcel
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1