How to Lookup data in different ways using 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)

Here,

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 from 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