### HLOOKUP Function Dictionary

Function Dictionary | Function Examples | Function Categories |

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