logo
  • Excel Charts
  • About David Aldred & This Blog
  • Consulting & Excel Support

Using Hlookups to bring back results in Excel

January 31, 2014 0 comments Article Easy, 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.

Related

Tags: Conditional, Excel, Formatting, Lookups

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Showing a Picture Based on the Value of another Cell in Excel
Using SUMPRODUCT to create a Conditional Weighted Average in Excel
Showing Data Bars as a 'Proportion to Total' in Excel
Format or Remove Borders from a Slicer or Timeline in Excel
Populate a Dropdown Based on the Value of Another Dropdown in PowerApps
Using the same Named Range in multiple worksheets, but with different values, in Excel

Meta

  • Register
  • Log in
  • Entries RSS
  • Comments RSS
  • WordPress.org

Categories

Pages

  • Excel Charts
  • About David Aldred & This Blog
  • Consulting & Excel Support

Copyright System Secrets 2021 - Theme by ThemeinProgress