Excel Function Friday: HLOOKUP Finds Current Price
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.
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.
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:
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:
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.
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:
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.