Use Of HLOOKUP
Jan
|
Feb
|
Mar
|
row 1
|
The row numbers are not needed.
|
10
|
80
|
97
|
row 2
|
they are part of the illustration.
|
20
|
90
|
69
|
row 3
| |
30
|
100
|
45
|
row 4
| |
40
|
110
|
51
|
row 5
| |
50
|
120
|
77
|
row 6
|
Type a month to look for :
|
Feb
| ||||
Which row needs to be picked out :
|
4
| ||||
The result is :
|
100
|
=HLOOKUP(F10,D3:F10,F11,FALSE)
|
What Does It Do ?
This function scans across the column headings at the top of a table to find a specified item.
When the item is found, it then scans down the column to pick a cell entry.
Syntax
=HLOOKUP(ItemToFind,RangeToLookIn,RowToPickFrom,SortedOrUnsorted)
The ItemToFind is a single item specified by the user.
The RangeToLookIn is the range of data with the column headings at the top.
The RowToPickFrom is how far down the column the function should look to pick from.
The Sorted/Unsorted is whether the column headings are sorted. TRUE for yes, FALSE for no.
Formatting
No special formatting is needed.
Example 1
This table is used to find a value based on a specified month and name.
The =HLOOKUP() is used to scan across to find the month.
The problem arises when we need to scan down to find the row adjacent to the name.
To solve the problem the =MATCH() function is used.
The =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, the =MATCH() number is 1 less than we require, so and extra 1 is
added to compensate.
The =HLOOKUP() now uses this =MATCH() number to look down the month column and
picks out the correct cell entry.
The =HLOOKUP() uses FALSE at the end of the function to indicate to Excel that the
column headings are not sorted, even though to us the order of Jan,Feb,Mar is correct.
If they were sorted alphabetically they would have read as Feb,Jan,Mar.
Jan
|
Feb
|
Mar
| |
Bob
|
10
|
80
|
97
|
Eric
|
20
|
90
|
69
|
Alan
|
30
|
100
|
45
|
Carol
|
40
|
110
|
51
|
David
|
50
|
120
|
77
|
Type a month to look for :
|
feb
| |||
Type a name to look for :
|
alan
| |||
The result is :
|
100
| |||
=HLOOKUP(F54,D47:F54,MATCH(F55,C48:C52,0)+1,FALSE)
| ||||
Example 2
This example shows how the =HLOOKUP() is used to pick the cost of a spare part for
different makes of cars.
The =HLOOKUP() scans the column headings for the make of car specified in column B.
When the make is found, the =HLOOKUP() then looks down the column to the row specified
by the =MATCH() function, which scans the list of spares for the item specified in column C.
The function uses the absolute ranges indicated by the dollar symbol $. This ensures that
when the formula is copied to more cells, the ranges for =HLOOKUP() and =MATCH() do
not change.
Maker
|
Spare
|
Cost
| |||||
Vauxhall
|
Ignition
|
£50
|
Vauxhall
|
Ford
|
VW
| ||
VW
|
GearBox
|
£600
|
GearBox
|
500
|
450
|
600
| |
Ford
|
Engine
|
£1,200
|
Engine
|
1000
|
1200
|
800
| |
VW
|
Steering
|
£275
|
Steering
|
250
|
350
|
275
| |
Ford
|
Ignition
|
£70
|
Ignition
|
50
|
70
|
45
| |
Ford
|
CYHead
|
£290
|
CYHead
|
300
|
290
|
310
| |
Vauxhall
|
GearBox
|
£500
| |||||
Ford
|
Engine
|
£1,200
| |||||
=HLOOKUP(B79,G72:I77,MATCH(C79,F73:F77,0)+1,FALSE)
| |||||||
Example 3
In the following example a builders merchant is offering discount on large orders.
The Unit Cost Table holds the cost of 1 unit of Brick, Wood and Glass.
The Discount Table holds the various discounts for different quantities of each product.
The Orders Table is used to enter the orders and calculate the Total.
All the calculations take place in the Orders Table.
The name of the Item is typed in column C.
The Unit Cost of the item is then looked up in the Unit Cost Table.
The FALSE option has been used at the end of the function to indicate that the product
names across the top of the Unit Cost Table are not sorted.
Using the FALSE option forces the function to search for an exact match. If a match is
not found, the function will produce an error.
=HLOOKUP(C127,E111:G112,2,FALSE)
The discount is then looked up in the Discount Table
If the Quantity Ordered matches a value at the top of the Discount Table the =HLOOKUP will
look down the column to find the correct discount.
The TRUE option has been used at the end of the function to indicate that the values
across the top of the Discount Table are sorted.
Using TRUE will allow the function to make an approximate match. If the Quantity Ordered does
not match a value at the top of the Discount Table, the next lowest value is used.
Trying to match an order of 125 will drop down to 100, and the discount from
the 100 column is used.
=HLOOKUP(D127,E115:G118,MATCH(C127,D116:D118,0)+1,TRUE)
Unit Cost Table
| ||||
Brick
|
Wood
|
Glass
| ||
£2
|
£1
|
£3
| ||
Discount Table
| ||||
1
|
100
|
300
| ||
Brick
|
0%
|
6%
|
8%
| |
Wood
|
0%
|
3%
|
5%
| |
Glass
|
0%
|
12%
|
15%
| |
Orders Table
| ||||
Item
|
Units
|
Unit Cost
|
Discount
|
Total
|
Brick
|
100
|
£2
|
6%
|
£188
|
Wood
|
200
|
£1
|
3%
|
£194
|
Glass
|
150
|
£3
|
12%
|
£396
|
Brick
|
225
|
£2
|
6%
|
£423
|
Wood
|
50
|
£1
|
0%
|
£50
|
Glass
|
500
|
£3
|
15%
|
£1,275
|
Unit Cost
|
=HLOOKUP(C127,E111:G112,2,FALSE)
| |||
Discount
|
=HLOOKUP(D127,E115:G118,MATCH(C127,D116:D118,0)+1,TRUE)
| |||
कोई टिप्पणी नहीं:
एक टिप्पणी भेजें