Confident Cells

HLOOKUP: tables that run across: practice exercises

HLOOKUP, row number, exact and approximate. 5 exercises on real-looking data, each marked the moment you press Check. The first exercise is below to try now; the full lesson is part of Pro.

Your turn

The pay table in G1:L3 runs across, with the grades along the top. In C2, look up each person's hourly rate.

ABCDEFGHIJKL
1EmployeeGradeHourly rateGradeABCDE
2BenDHourly rate£12.84£14.16£15.34£15.63£18.24
3KerryAOvertime rate£19.26£21.24£23.01£23.45£27.36
4OwenB
5NiaD
6MariaA
7NadiaD
8LiamC
9MarekA
10AlexE
11JordanC

How it works

HLOOKUP(lookup value, table, row number, FALSE) is VLOOKUP turned on its side. It looks along the top row of the table for the value, then goes down to the row number you give and returns what's there.

Use FALSE for an exact match, as with VLOOKUP. Use TRUE when the top row holds the lowest value of each band, in order from smallest to largest. Lock the table with $ when the formula is copied.

=HLOOKUP(B5,$B$1:$D$2,2,FALSE) =HLOOKUP(F1,B1:G3,3,FALSE) =HLOOKUP(B2,$G$1:$K$2,2,TRUE)

Looking across instead of down

VLOOKUP needs a table that runs down, with the things you look up in the first column. Some tables run across: months, grades or sizes along the top row.

HLOOKUP(lookup value, table, row number, FALSE) looks along the top row, then goes down to the row number you give. Row 1 is the top row itself.

Worked example

ABCD
1GradeABC
2Rate£12.00£14.00£17.00
3
4NameGradeRate
5AmiraB
6BenC
  1. =HLOOKUP(B5, in . What are we looking for? Amira's grade, in B5.
  2. =HLOOKUP(B5,$B$1:$D$2, in . The table runs across B1:D2, with the grades along the top. Lock it with $, as the formula will be copied down.
  3. =HLOOKUP(B5,$B$1:$D$2,2,FALSE) in . The rates are in row 2 of the table, and FALSE asks for an exact match. Grade B gives £14.
  4. =HLOOKUP(B5,$B$1:$D$2,2,FALSE) in . Copied down, Ben's grade C gives £17.

Bands across the top

As with VLOOKUP, TRUE finds the band a value falls into. The top row must hold the lowest value of each band, smallest first: 0, 100, 250, 500.

An order of 320 isn't in the top row, so HLOOKUP takes the last value that is not more than 320, which is 250.

What you'll practise

  1. The pay table in G1:L3 runs across, with the grades along the top. In C2, look up each person's hourly rate.
  2. Overtime is paid at the overtime rate in row 3 of the pay table. In D2, work out each person's overtime pay: their overtime hours times their overtime rate.
  3. Make a quick look-up. In J2, show the actual spend for the month typed in J1.
  4. The discount table in G1:L2 gives the smallest order value for each rate. In C2, look up the discount each customer gets.
  5. The HLOOKUP in C2 works, but when it's copied down most of the rates go wrong. Fix the formula in C2 so it works all the way down.