VLOOKUP ()
This function lookups and retrieves data from a specified
column in table.
Best view in desktop mode.
A
|
B
|
C
|
D
|
E
|
F
|
|
1
|
column_1
|
column_2
|
column_3
|
column_4
|
column_5
|
|
2
|
Emp ID
|
First Name
|
Last Name
|
Email ID
|
Salary
|
|
3
|
810
|
Neeraj
|
Kumar
|
neeraj.kumar@gmail.com
|
₹ 65,000
|
|
4
|
898
|
Syed
|
Azam
|
syed.azam@gmail.com
|
₹ 72,000
|
|
5
|
941
|
Rahul
|
Yadav
|
rahul.yadav@gmail.com
|
₹ 53,000
|
|
6
|
986
|
Andrew
|
Shulman
|
andrew.shulman@gmail.com
|
₹ 98,000
|
|
7
|
722
|
Brandan
|
Jeorge
|
brandan.jeorge@gmail.com
|
₹ 85,000
|
|
8
|
701
|
Vijay
|
Yadav
|
vijay.yadav@gmail.com
|
₹ 91,000
|
|
9
|
969
|
Saif
|
Ali
|
saif.ali@gmail.com
|
₹ 65,000
|
|
10
|
967
|
Ryan
|
Jourdan
|
ryan.jourdan@gmail.com
|
₹ 82,000
|
|
11
|
885
|
Matthew
|
Boltz
|
matthew.boltz@gmail.com
|
₹ 78,000
|
|
12
|
748
|
Akshay
|
Kumar
|
akshay.kumar@gmail.com
|
₹ 69,000
|
|
13
|
||||||
14
|
Enter Emp ID
|
967
|
Put the emp id here
|
|||
15
|
First Name
|
Ryan
|
=VLOOKUP(B14,A2:E12,2,0)
|
|||
16
|
Last Name
|
Jourdan
|
=VLOOKUP(B14,A2:E12,3,False)
|
|||
17
|
Email ID
|
ryan.jourdan@gmail.com
|
=VLOOKUP(B14,A2:E12,4,0)
|
|||
18
|
Salary
|
₹ 82,000
|
=VLOOKUP(B14,A2:E12,5,0)
|
|||
19
|
||||||
It supports approximate and exact matching, and wildcards
(*?) for partial matching.
“V” stand for vertical and lookup values must be appear in
first column of table.
Vlookup looks and match value from left to right.
Vlookup retrieves data based on column number
Syntax
=VLOOKUP
(ItemToFind, RangeToLookIn, ColumnToPickFrom, SortedOrUnsorted)
=VLOOKUP
(lookup_value, table_array, col_index_num, [range_lookup])
Where
Lookup_value
= this is value to look in the first column
of first table.
table_array
= the selection of second table from which
to retrieve a value.
col_index_num
= the column number in the table to retrieve value.
[range_lookup]
= True for approximate value or False for exact value to retrieve. We also use
1 for true and 0 for false.
Formatting
No special
formatting is needed.
Example 1
Vlookup
can be used as per your requirement. You can match all details for one record and you can match all details or partial details for multiple records.
It can lookup values from other sheets and
workbook also. You can freeze source data range by pressing F4. Like
Table data A24:E34, select this range and press F4, then
range will change to $A$24:$E$34
A
|
B
|
C
|
D
|
E
|
F
|
|
23
|
column_1
|
column_2
|
column_3
|
column_4
|
column_5
|
|
24
|
Emp ID
|
First Name
|
Last Name
|
Email ID
|
Salary
|
|
25
|
810
|
Neeraj
|
Kumar
|
neeraj.kumar@gmail.com
|
₹ 65,000
|
|
26
|
898
|
Syed
|
Azam
|
syed.azam@gmail.com
|
₹ 72,000
|
|
27
|
941
|
Rahul
|
Yadav
|
rahul.yadav@gmail.com
|
₹ 53,000
|
|
28
|
986
|
Andrew
|
Shulman
|
andrew.shulman@gmail.com
|
₹ 98,000
|
|
29
|
722
|
Brandan
|
Jeorge
|
brandan.jeorge@gmail.com
|
₹ 85,000
|
|
30
|
701
|
Vijay
|
Yadav
|
vijay.yadav@gmail.com
|
₹ 91,000
|
|
31
|
969
|
Saif
|
Ali
|
saif.ali@gmail.com
|
₹ 65,000
|
|
32
|
967
|
Ryan
|
Jourdan
|
ryan.jourdan@gmail.com
|
₹ 82,000
|
|
33
|
885
|
Matthew
|
Boltz
|
matthew.boltz@gmail.com
|
₹ 78,000
|
|
34
|
748
|
Akshay
|
Kumar
|
akshay.kumar@gmail.com
|
₹ 69,000
|
|
35
|
||||||
36
|
||||||
37
|
Emp ID
|
Salary
|
||||
38
|
810
|
₹ 65,000
|
=VLOOKUP(A38,$A$24:$E$34,5,0)
|
|||
39
|
986
|
₹ 98,000
|
=VLOOKUP(A39,$A$24:$E$34,5,0)
|
|||
40
|
885
|
₹ 78,000
|
=VLOOKUP(A40,$A$24:$E$34,5,0)
|
|||
41
|
967
|
₹ 82,000
|
=VLOOKUP(A41,$A$24:$E$34,5,0)
|
|||
42
|
748
|
₹ 69,000
|
=VLOOKUP(A42,$A$24:$E$34,5,0)
|
|||
43
|
||||||
Example 2
Vlookup
looks and match records from left to right. If you want to match with Emp ID,
then you can match only “Email ID”
and “Salary” column.
A
|
B
|
C
|
D
|
E
|
F
|
|
23
|
column_1
|
column_2
|
column_3
|
|||
24
|
First Name
|
Last Name
|
Emp ID
|
Email ID
|
Salary
|
|
25
|
Neeraj
|
Kumar
|
810
|
neeraj.kumar@gmail.com
|
₹ 65,000
|
|
26
|
Syed
|
Azam
|
898
|
syed.azam@gmail.com
|
₹ 72,000
|
|
27
|
Rahul
|
Yadav
|
941
|
rahul.yadav@gmail.com
|
₹ 53,000
|
|
28
|
Andrew
|
Shulman
|
986
|
andrew.shulman@gmail.com
|
₹ 98,000
|
|
29
|
Brandan
|
Jeorge
|
722
|
brandan.jeorge@gmail.com
|
₹ 85,000
|
|
30
|
Vijay
|
Yadav
|
701
|
vijay.yadav@gmail.com
|
₹ 91,000
|
|
31
|
Saif
|
Ali
|
969
|
saif.ali@gmail.com
|
₹ 65,000
|
|
32
|
Ryan
|
Jourdan
|
967
|
ryan.jourdan@gmail.com
|
₹ 82,000
|
|
33
|
Matthew
|
Boltz
|
885
|
matthew.boltz@gmail.com
|
₹ 78,000
|
|
34
|
Akshay
|
Kumar
|
748
|
akshay.kumar@gmail.com
|
₹ 69,000
|
|
35
|
Example 3
Vlookup
looks and match records from top to down i.e. vertical. If you want to match
records from row 29 with cell address C29,
then it will match only records from row 29.
A
|
B
|
C
|
D
|
E
|
F
|
|
23
|
column_1
|
column_2
|
column_3
|
|||
24
|
First Name
|
Last Name
|
Emp ID
|
Email ID
|
Salary
|
|
25
|
Neeraj
|
Kumar
|
810
|
neeraj.kumar@gmail.com
|
₹ 65,000
|
|
26
|
Syed
|
Azam
|
898
|
syed.azam@gmail.com
|
₹ 72,000
|
|
27
|
Rahul
|
Yadav
|
941
|
rahul.yadav@gmail.com
|
₹ 53,000
|
|
28
|
Andrew
|
Shulman
|
986
|
andrew.shulman@gmail.com
|
₹ 98,000
|
|
29
|
Brandan
|
Jeorge
|
722
|
brandan.jeorge@gmail.com
|
₹ 85,000
|
|
30
|
Vijay
|
Yadav
|
701
|
vijay.yadav@gmail.com
|
₹ 91,000
|
|
31
|
Saif
|
Ali
|
969
|
saif.ali@gmail.com
|
₹ 65,000
|
|
32
|
Ryan
|
Jourdan
|
967
|
ryan.jourdan@gmail.com
|
₹ 82,000
|
|
33
|
Matthew
|
Boltz
|
885
|
matthew.boltz@gmail.com
|
₹ 78,000
|
|
34
|
Akshay
|
Kumar
|
748
|
akshay.kumar@gmail.com
|
₹ 69,000
|
|
35
|
No comments:
Post a Comment