Excel Function Friday: HLOOKUP Finds Current Price

iconlookup2 On Day 10 of the 30 Excel Functions in 30 Days series, we looked at the Excel HLOOKUP function. It’s similar to VLOOKUP, but looks for values in a horizontal list, instead of a vertical list.

The second example in that HLOOKUP blog post showed how to find a rate in a lookup table, based on the date entered in cell C5. On March 15th, the rate would be 0.25, because the Jan 1st rate is still in effect.

Hlookup03

Beyond One Cell

In the comments for the HLOOKUP blog post, Fred said that he got the formula working correctly in cell D5, but wondered how to use the result in multiple cells.

In this example, we’ll use the rates as a lookup for pricing. The prices change quarterly, and the correct price will be used in each order, based on the order date.

HLookupRates03

Set Up the Lookup Table

In this workbook, the table with the quarterly dates and rates is on a separate sheet, named Rates. New rates will be added each quarter, so we’ll create a dynamic range named RateTable, using the technique from Example 3 in the 30XL30D INDEX function post.

In this HLOOKUP rates table, the formula for the named range is:

=Rates!$A$1:INDEX(Rates!$2:$2,1,COUNT(Rates!$2:$2))

HLookupRates01

Create the HLOOKUP Formula

In the Orders table, we’ll use an Excel HLOOKUP formula to pull the correct rate from the RateTable range, based on the order date.

In cell B2, the formula is:

=HLOOKUP(A2,RateTable,2)

The final argument is omitted, so the result is an approximate match. If the order date isn’t found in the first row of the RateTable range, the HLOOKUP formula result is based on the next largest date that is less than order date.

HLookupRates02

Add the Pricing Formula

The final step is to add the pricing formula in column D. Quantities will be entered in column C, so the pricing formula will multiply the quantity by the rate.

The formula in cell D2 is:

=B2*C2

HLookupRates03

Download the Sample File

To see the Excel HLOOKUP formula and the RateTable named range, you can download the HLOOKUP Rates sample file. It is in Excel 2007 format, and zipped.

_______________

You may also like...

5 Responses

  1. Can you explain =Rates!$A$1:INDEX(Rates!$2:$2,1,COUNT(Rates!$2:$2))? How it is providing a range?

  2. In the above article, you can follow the link to the INDEX blog post, where that function is explained.

    INDEX returns a reference, and in this example, that reference is used as the last cell in the range.

  3. Gregory says:

    I had to use the Evalutate Formula dialog box to figure out the Rate Table was returning the range A1:D2 because, for some reason, I couldn’t visualize how the Named Range formula was doing it’s magic. Guess I better get a second cup of coffee!

  4. Contextures Blog » Excel Function Friday: INDEX for Dynamic Range says:

    […] Excel Function Friday: HLOOKUP Finds Current Price […]

  5. joy mukherjee says:

    PLEASE INFORM ME DETAIL H LOOKUP. with example.

Leave a Reply

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