HLOOKUP ()
This function lookups and retrieves data from a specified row
in table.
Best view in desktop mode.
A
|
B
|
C
|
D
|
E
|
F
|
|
1
|
Jan
|
Feb
|
Mar
|
Apr
|
row_1
|
|
2
|
810
|
701
|
589
|
785
|
row_2
|
|
3
|
898
|
969
|
462
|
658
|
row_3
|
|
4
|
941
|
967
|
248
|
548
|
row_4
|
|
5
|
986
|
885
|
528
|
856
|
row_5
|
|
6
|
722
|
748
|
485
|
965
|
row_6
|
|
7
|
||||||
8
|
||||||
9
|
Enter Month
|
Feb
|
Put Month Name Here
|
|||
10
|
Row Number
|
5
|
Put Row Number Here
|
|||
11
|
Count
|
885
|
=HLOOKUP(B9,A1:D6,B10,0)
|
|||
12
|
||||||
It supports approximate and exact matching, and wildcards
(*?) for partial matching.
“H” stand for horizontal and lookup values must appear in the first row of the table.
Hlookup looks and matches the value from up to down.
Hlookup retrieves data based on the row number
Syntax
=HLOOKUP (ItemToFind,
RangeToLookIn, RowToPickFrom, SortedOrUnsorted)
=HLOOKUP (lookup_value,
table_array, row_index_num, [range_lookup])
Where
Lookup_value = this is the value to look at in the first column
of the first table.
table_array = the
selection of the second table from which to retrieve a value.
col_index_num = the
row number in the table to retrieve a 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
This table is used to find a value based on a specified month
and name.
=HLOOKUP() is used to scan across to find the
month.
=MATCH() looks through the list of names to
find the name we require.
It then calculates the position of the name in the list.
Unfortunately, because the list of names is not as deep as the lookup range, =MATCH() the number is 1 less than we require, so add an extra 1 to compensate.
=HLOOKUP() now uses this =MATCH() number to look down the
month column and picks out the correct cell entry.
=HLOOKUP() uses FALSE
or 0
at the end of the function to indicate to Excel that the column headings are
not sorted, however for us the order of Jan, Feb, Mar, Apr is correct. If they
were sorted alphabetically they would have read as Apr, Feb, Jan, and Mar.
A
|
B
|
C
|
D
|
E
|
F
|
G
|
|
16
|
Name
|
Jan
|
Feb
|
Mar
|
Apr
|
row_1
|
|
17
|
Saifi
|
810
|
701
|
589
|
785
|
row_2
|
|
18
|
Nitin
|
898
|
969
|
462
|
658
|
row_3
|
|
19
|
Matt
|
941
|
967
|
248
|
548
|
row_4
|
|
20
|
Vijay
|
986
|
885
|
528
|
856
|
row_5
|
|
21
|
Andrew
|
722
|
748
|
485
|
965
|
row_6
|
|
22
|
|||||||
23
|
|||||||
24
|
Enter Month
|
Mar
|
Put Month Name Here
|
||||
25
|
Enter Name
|
Vijay
|
Put Name Here
|
||||
26
|
Count
|
528
|
'=HLOOKUP(B24,A16:E21,MATCH(B25,A17:A21,0)+1,0)
|
||||
27
|
|||||||
No comments:
Post a Comment