Best Microsoft Excel Bloggers

Tuesday, March 30, 2010



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.

Since I want to retrieve the Total which is at row that becomes my row index number.

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

Ms. Excel- Resident Excel Geek