VLOOKUP Function in Microsoft Excel

Brief Introduction

           VLOOKUP is a Microsoft Excel function for lookup and retrieves data from a specific column in the table. VLOOKUP Function supports approximate and exact matching, and wildcards (*?) for partial matches. The "V" stands for "vertical". Lookup values must appear in the first column of the table, with lookup columns to the right.

Purpose:

Lookup a value in a table by matching on the first Column.

Syntax of VLOOKUP Function:

The syntax of VLOOKUP Function in Excel is as follows;

=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

Return value:

The matched value from a table.

Arguments:

•      Value - The value to look up.
•      Table - The table from which to retrieve data.
•      Col_index - The Column number from which to retrieve data.
•       Range_lookup - [optional] this option indicate exact match or approximate match. Default =     TRUE = approximate match.

Match type:

When we are using a test with VLOOKUP Function, we can use any of the following match types,

Match Type	Behavior	Details
TRUE	Approximate Match	MATCH finds the smallest value greater than or equal to lookup value. Lookup array must be sorted in descending order.
FALSE	Exact Match	MATCH finds the value exactly equal to lookup value. Lookup array does not need to be sorted.

You can download free the Complete Practice of Concern Topic of Microsoft Excel File, Click on the below download icon,

             

           This article explains in simple terms how to use the VLOOKUP Function. It takes a step-by-step approach, & following below contents we will touch in this post.

1.      VLOOKUP Function explanation through various examples
2.      How to use Wildcards in VLOOKUP Function

Let’s start to learn something new about the VLOOKUP Function of Microsoft Excel.                

1.  VLOOKUP Function-Example 1.

             VLOOKUP Function is used to find out the exact match or Approximate match Vertical value, as we explain this formula through a given below example,

Explanation: -  The above specific given information in "B2 to E8" is our criteria for given commission to the agent on the sale of specific Equipment, As by using the VLOOKUP Function, we can get our proper answer, In the example, the selected cell "C11" show the formula that how we use it,          

In this scenario, we have used a formula like below,

=VLOOKUP(B11,C3:E8,2,FALSE)

As in above formula the Lookup value is $100,000/- While Column index Number is "2" (it mean that from selected table array "C3:E8", we need a return from the second column "Commision %", if we need Sale figure then column Index number maybe 1), So as the value $100,000/- is equal or exact match to "12%" that "12% Commision" should be pay to agent, So the formula calculate the exact answer.

Example 2.

             By using above same information or data this time we use text ("Water Cooler" or "any other word") instead of a Lookup value, see in the below example.

Explanation: - As according to the given formula in cell "C11", the text or word "Water Cooler" is used & the Column from which we want to result should be that is "3", so according to our selected criteria, the Column "3" name is "Commision %" & there exact match result is "12%".

In this scenario, we have used a formula like below,

=VLOOKUP("Water Cooler",B3:E8,3,FALSE)

2. How to use Wildcards in VLOOKUP Function.

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

•     Question mark (?) to match any a single character, and so on.
•     Asterisk (*) to match any sequence of characters.

Using wildcard characters may prove really useful in many cases:

• When we do not remember the exact text we are looking for.
• When we 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 we 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, we may rack our brain trying to figure out why the normal formula does not work. 

Example 1.

           So let’s start practice on wildcard characters, below a few examples, I have taken,

Explanation: - According to example, if we know about the first three characters of a name & not have any other information about the whole name, so in that case, we use the formula of VLOOKUP Function in Cell B12 in which the Lookup value "mic*" (Put the wildcard * at the end of Characters), the table is "$B$3: $E$8", The column index number is "1" (because we want to find out the value from Column 1 which in Name Column), So the lookup value exactly Find out which is "Microwave Oven".

In this scenario, we have used a formula like below,

=VLOOKUP("mic*",$B$3:$E$8,1,FALSE)

Example 2.

           If we have information about the last few Characters of a Name or Words then how we lookup it through VLOOKUP Function, See in below example,

Explanation: - According to above example, if we know about the last four characters of a name or word & not have any other information about the whole name, so in that case, we use the formula of VLOOKUP Function in Cell B12 in which the Lookup value "*sion"(Put the wildcard * at the start of Characters), the table is "B3: E8", Column index number is "1" (because we want to find out the value from Column 1 which in Name Column), So the lookup value exactly Find out which is "Television".

In this scenario, we have used a formula like below,

=VLOOKUP("*sion",B3:E8,1,FALSE)

Example 3.

            If we have information about the first & last few Characters of a Name or Words then how we lookup it through VLOOKUP Function, See in below example,

Explanation: - According to above example, if we know about the first & last three characters of a name or word & not have any other information about the whole name so in that case we use the formula of VLOOKUP Function in Cell "B12" in which the Lookup value "was*ine"(Put the wildcard * between the Characters), the table is "B3: E8", the Column index number is "1" (because we want to find out the value from Column 1 which in Name Column), So the lookup value exactly Find out which is "Washing Machine".

In this scenario, we have used a formula like below,

=VLOOKUP("was*ine",B3:E8,1,FALSE)

Example 4.

            If we have only information about the number of characters in specific Name or Words then how we lookup it through VLOOKUP Function, See in below example,

Explanation: - According to the example, we know that a name or word by six characters we have in the list  & not have any other information about whole name, so in that case we use the formula of VLOOKUP Function in Cell B12 in which the Lookup value "??????"(Put the wildcard ? in the " "), table is "$B$3: $E$8", Column index number is "1" (because we want to find out the value from Column 1 which in Name Column), So the lookup value exactly Find out which is "Juicer".

In this scenario, we have used a formula like below,

=VLOOKUP("??????",$B$3:$E$8,1,FALSE)