Whenever anyone thinks about a lookup function, the first thought is typically Vlookup. Sometimes however a Hlookup will do the trick. Both functions are virtually identical except that Vlookup searches vertically while the Hlookup - yes you guessed it- searches hortizontally. ( For my purposes I deliberatley excluded discussion of Index Match for those of you wondering.)
In the example below, I am using the HLOOKUP function to retrieve the Total budgeted expense for whatever month happens to appear in cell C9.
My table_array which I called budget_expenses is C1.N7. You don't really need the table array since we are not copying the formula anywhere- but it is a good habit to get into.
I know that what I am looking for is in the table so FALSE is not really needed - but again it is a good habit to get into particularly if you can't type or spell :)
So, essentially, Excel looks at the table array, and looks across the top row for a match with what is in C9.
In my example, it is January. When it finds a matching column, it then goes down 7 rows and retrieves the value it finds.
1. Select the cell range C1:N7.
2. Name the cell range Budget_Expenses.
3. Click on cell C10 - this is where the answer will display.
4. Enter a HLOOKUP function in cell C10 that returns the Total value. Use Budget_Expenses as the table array and cell C9 as the lookup value.
(Hint: Total is the 7th row).
5. The answer for January should be $31,650. Change B9 to November and watch the total change.
If you use a Data Validation in C9, you will save yourself some time and typing errors down the road.
No comments:
Post a Comment