hlookup函数使用教程_星期五的Excel函数：HLOOKUP查找当前价格

hlookup函数使用教程

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.

在30天系列中30个Excel函数系列的第10天，我们研究了Excel HLOOKUP函数 。 它与VLOOKUP相似，但是在水平列表而不是垂直列表中查找值。

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.

该HLOOKUP博客文章中的第二个示例说明了如何根据在单元格C5中输入的日期在查找表中查找费率。 由于1月1日的汇率仍然有效，因此3月15日的汇率为0.25。

超越一个细胞 (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.

在HLOOKUP博客文章的评论中，Fred说他使公式在单元格D5中正常工作，但想知道如何在多个单元格中使用结果。

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.

在此工作簿中，带有季度日期和费率的表位于单独的表中，称为费率。 每个季度都会添加新的费率，因此，我们将使用30XL30D INDEX函数中的示例3的技术创建一个名为RateTable的动态范围。

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

在此HLOOKUP费率表中，命名范围的公式为：

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

=费率！$ A $ 1：INDEX(费率！$ 2：$ 2,1，COUNT(费率！$ 2：$ 2))

创建HLOOKUP公式 (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.

在“订单”表中，我们将使用Excel HLOOKUP公式根据订单日期从RateTable范围中提取正确的汇率。

In cell B2, the formula is:

在单元格B2中，公式为：

=HLOOKUP(A2,RateTable,2)

= 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.

如果在RateTable范围的第一行中找不到订单日期，则HLOOKUP公式结果将基于小于订单日期的下一个最大日期。

添加定价公式 (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.

最后一步是在D列中添加定价公式。数量将输入到C列中，因此定价公式会将数量乘以费率。

The formula in cell D2 is:

单元格D2中的公式为：

=B2*C2

= B2 * C2

下载样本文件 (Download the Sample File)

To see the Excel HLOOKUP formula and the RateTable named range, you can download the HLOOKUP Rates sample file.

要查看Excel HLOOKUP公式和RateTable命名范围，可以下载HLOOKUP Rates示例文件 。

It is in Excel 2007 format, and zipped. _______________

它是Excel 2007格式，并已压缩。 _______________

翻译自: https://contexturesblog.com/archives/2011/02/18/excel-function-friday-hlookup-finds-current-price/

hlookup函数使用教程