Using Hlookups to bring back results in Excel

Today I want to do a quick page on using Hlookups in Excel.

Hlookups work in much the same way as a Vlookup (H being Horiztonal Lookup rather than V being Vertical Lookup) but often people forget about it or afraid to use it, instead favouring a more complex approach to the problem.

For this example, I’ve got a simple meal planner by day and want Excel to tell me on any given day what I should be eating.

My range looks like this…

I’ve also named my range as ‘Menu’ to make my formula tidier.

To do this, highlight the range

Type ‘Menu’ in the ‘Name box’ to the left of the formula bar and hit [Enter]

I then have my current day’s plan…

Where ‘Friday’ is simply the formula formatting as ‘dddd’ (Right Click the Cell à Format Cells à Custom à dddd à ok)

Next to each meal name is then a formula looking up the meal name, then pulling back the relevant day based on the formula

This formula brings back the current day of the week as an integer – you can pick between having 0 to 6 or 1 to 7, and which day of the week to start on by changing the 2 in the formula.

My formula then uses this integer to know how many rows to count down.

H12 is the meal name (eg ‘Breakfast’), Menu is my names range with my meal plan, Weekday+1 gives the day of the week+1 (to account for the extra row at the top), FALSE tells Excel I want an exact match.

Copy this formula down for each meal. On each day, the day of the week in yellow will change forcing the Hlookup to bring back the next day’s meal plan.