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.
The pay table in G1:L3 runs across, with the grades along the top. In C2, look up each person's hourly rate.
| A | B | C | D | E | F | G | H | I | J | K | L | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Employee | Grade | Hourly rate | Grade | A | B | C | D | E | |||
| 2 | Ben | D | Hourly rate | £12.84 | £14.16 | £15.34 | £15.63 | £18.24 | ||||
| 3 | Kerry | A | Overtime rate | £19.26 | £21.24 | £23.01 | £23.45 | £27.36 | ||||
| 4 | Owen | B | ||||||||||
| 5 | Nia | D | ||||||||||
| 6 | Maria | A | ||||||||||
| 7 | Nadia | D | ||||||||||
| 8 | Liam | C | ||||||||||
| 9 | Marek | A | ||||||||||
| 10 | Alex | E | ||||||||||
| 11 | Jordan | C |
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
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Grade | A | B | C |
| 2 | Rate | £12.00 | £14.00 | £17.00 |
| 3 | ||||
| 4 | Name | Grade | Rate | |
| 5 | Amira | B | ||
| 6 | Ben | C |
=HLOOKUP(B5,in . What are we looking for? Amira's grade, in B5.=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.=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.=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
- The pay table in G1:L3 runs across, with the grades along the top. In C2, look up each person's hourly rate.
- 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.
- Make a quick look-up. In J2, show the actual spend for the month typed in J1.
- The discount table in G1:L2 gives the smallest order value for each rate. In C2, look up the discount each customer gets.
- 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.