**Excel VLOOKUP Function:**

Vlookup Funciton
searches for a value in the first column of a table array and returns a value
in the same row from another column in the table array.

The V in VLOOKUP
stands for vertical. Use VLOOKUP instead of HLOOKUP when your comparison values
are located in a column to the left of the data that you want to find.

**Syntax :**

=VLOOKUP(lookupValue,TableArray,ColIndexNum,RangeLookup)

**Where:**

LookupValue : The value to search in the first column
of the table array. LookupValue can be a value or a reference. If LookupValue
is smaller than the smallest value in the first column of TableArray, VLOOKUP
returns the #N/A error value.

TableArray : Two or more columns of
data. Used as a reference to a range or a range name. The values in the first
column of TableArray are the values
searched by lookupValue. These values can be text, numbers, or logical values.
Uppercase and lowercase text are equivalent.

ColIndexNum : The column number in TableArray from which the matching value must be
returned. A ColIndexNum of 1 returns the value in the first column in
TableArray ; a ColIndexNum of 2 returns
the value in the second column in TableArray
, and so on. If ColIndexNum is:

Less than 1, VLOOKUP
returns the #VALUE! error value.

If ColIndexNum is Greater than the number of columns in
TableArray , VLOOKUP returns the #REF!
error value.

RangeLookup : A logical value that specifies whether
you want VLOOKUP to find an exact match
or an approximate match:

If

**TRUE**or omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than LookupValue is returned.
The values in the
first column of TableArray must be placed
in ascending sort order; otherwise, VLOOKUP may not give the correct value. For
more information.

If

**FALSE**, VLOOKUP will only find an exact match. In this case, the values in the first column of TableArray do not need to be sorted. If there are two or more values in the first column of TableArray that match the lookupValue, the first value found is used. If an exact match is not found, the error value #N/A is returned.
Remarks

**Remarks :**

Ø When searching text values in the first column of
table_array, ensure that the data in the first column of table_array does not
have leading spaces, trailing spaces, inconsistent use of straight ( ' or
" ) and curly ( ‘ or “) quotation marks, or nonprinting characters. In
these cases, VLOOKUP may give an incorrect or unexpected value. For more
information, see CLEAN and TRIM Funcitons.

Ø When searching number or date values, ensure that the
data in the first column of table_array is not stored as text values. In this
case, VLOOKUP may give an incorrect or unexpected value. For more information,
see Convert numbers stored as text to numbers.

Ø If range_lookup is FALSE and lookup_value is text, then
you can use the wildcard characters, question mark (?) and asterisk (*), in
lookup_value. A question mark matches any single character; an asterisk matches
any sequence of characters. If you want to find an actual question mark or
asterisk, type a tilde (~) preceding the character.

----------------------------------------------------------------------------------------------------------------

**Examples :**

----------------------------------------------------------------------------------------------------------------

**Vlookup-Basic Model 1 :**

Let us suppose there seven months as

**Jan ……June ,**Ten names as**Name1…..Name10**.
Now if you want to lookup
the value of a particular name (say

**Name5**) in the desired column(say**Apr**).Then you can find using simple**Vlookup**Function in the following manner( explained in the following screen shots) .
----------------------------------------------------------------------------------------------------------------

**Model 2 - Vlookup**with

**Match Function:**

Let us suppose there seven months as

**Jan ……June ,**Ten names as**Name1…..Name10**.
Now if you want to lookup
the value of a particualar name (say

**Name5**) and particualar month(say**Apr**) selected from the list of names and months availble.Then you find using**Vlookup**and**Match**Functions Combination in the following manner( explained in the following screen shots) .**Syntax:**

=VLOOKP(B14,A2:G11,MATCH(C13,A1:G1,0),0)

**Explanation**

**:**

Here the above formula will
loolup the cell

**‘B14’**value in the table array**‘A2:G11’**and returns a value that matches to cell**‘C13’.**

**Model 3 -Vlookup**with

**Columns Function:**

**Syntax:**

=VLOOKUP(B14,A2:G11,COLUMNS(A:G),0)

**Explanation**

**:**

Here the

**COLUMNS (A:G)**function returns values form the column**‘G’**which matches to the**lookup value B14(Name 7).**
In the same way if you take

**COUMNS(A:G)**it will**return the value from the column****‘G’**which Macthes to the lookup value.
Thanks,

TAMATAM

## No comments:

## Post a Comment