hlookup函数多个条件
For day 10 in the 30XL30D challenge, we'll examine the HLOOKUP function. Not too surprisingly, this function is very similar to VLOOKUP, and works with items that are in a Horizontal list.
对于30XL30D挑战的第10天，我们将研究HLOOKUP函数。 毫不奇怪，此功能与VLOOKUP非常相似，并且适用于“水平”列表中的项目。
Poor HLOOKUP isn't as popular as its sibling though, because most tables are set up with the lookup values listed vertically. When was the last time that you wanted to search for a value across a row, and then return a value from that column, in a specific row below?
可怜的HLOOKUP并不像它的兄弟姐妹那样受欢迎，因为大多数表都是使用垂直列出的查找值设置的。 您是什么时候最后一次想要在一行中搜索一个值，然后从下面的特定行中的该列中返回一个值？
NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file -- the 30 Excel Functions in 30 Days eBook Kit ($10).
注意：您可以在一个易于使用的单个参考文件中获得这30个函数的全部内容-30天电子书工具包中的30个Excel函数 (10美元)。
Anyway, let's give HLOOKUP its moment in the spotlight, and take a look at its information and examples. Remember, if you have other tips or examples, please share them in the comments.
无论如何，让我们在关注HLOOKUP的时刻，并看一下它的信息和示例。 请记住，如果您还有其他提示或示例，请在评论中分享。
功能10：HLOOKUP (Function 10: HLOOKUP)
The HLOOKUP function looks for a value in the first row of a table, and returns another value from the same column in that table.
HLOOKUP函数在表的第一行中查找值，然后从该表的同一列中返回另一个值。
您如何使用HLOOKUP？ (How Could You Use HLOOKUP?)
The HLOOKUP function can find exact matches in the lookup row, or the closest match, so it can:
HLOOKUP函数可以在查找行中找到精确匹配项，或者是最接近的匹配项，因此它可以：
- Find the sales total in a selected region 查找选定区域的销售总额
- Find rate in effect on selected date 查找选定日期的有效汇率
HLOOKUP语法 (HLOOKUP Syntax)
The HLOOKUP function has the following syntax:
HLOOKUP函数具有以下语法：
-
lookup_value: the value that you want to look for -- it can be a value, or a cell reference.
lookup_value ：您要查找的值-它可以是一个值或单元格引用。
table_array: the lookup table -- this can be a range reference or a range name, with 2 or more columns.
table_array ：查找表-可以是范围参考或范围名称，具有2列或更多列。
row_index_num: the row that has the value you want returned, based on the row number within the table.
row_index_num ：基于表中的行号，具有要返回值的行。
[range_lookup]: for an exact match, use FALSE or 0; for an approximate match, use TRUE or 1, with the lookup value row sorted in ascending order.
[range_lookup] ：要完全匹配，请使用FALSE或0; 对于近似匹配，请使用TRUE或1，并且查找值行以升序排序。
HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
HLOOKUP(lookup_value，table_array，row_index_num，range_lookup)
陷阱陷阱 (HLOOKUP Traps)
Like VLOOKUP, the HLOOKUP function can be slow, especially when doing a text string match, in an unsorted table, where an exact match is requested. Wherever possible, use a table that is sorted by the first row, in ascending order, and use an approximate match. You can use MATCH or COUNTIF to check for the value first, to make sure it is in the table's first row.
像VLOOKUP一样，HLOOKUP函数的运行速度可能很慢，尤其是在进行文本字符串匹配时，在未排序的表中要求精确匹配的情况下尤其如此。 尽可能使用按第一行升序排列的表，并使用近似匹配。 您可以使用MATCH或COUNTIF首先检查该值，以确保它在表的第一行中。
Other functions, such as INDEX and MATCH, can be used to return values from a table, and are more efficient. We'll look at those functions later in the challenge, and see how flexible and powerful they are.
其他函数(例如INDEX和MATCH)可用于从表中返回值，并且效率更高。 在挑战赛的后面，我们将介绍这些功能，并了解它们的灵活性和强大性。
示例1：查找选定区域的销售 (Example 1: Find the Sales for a Selected Region)
The HLOOKUP function looks for a value in the top row of the lookup table. In this example, we'll find the sales total for a selected region.
HLOOKUP函数在查找表的第一行中查找值。 在此示例中，我们将找到选定区域的销售总额。
It's important to get the correct amount, so the following settings are used:
获取正确的数量很重要，因此使用以下设置：
- a region name is entered in cell B7 在单元格B7中输入区域名称
- the region lookup table has two rows, and is in range C2:F3 区域查找表有两行，范围为C2：F3
- sales total is in row 2 of the table. 销售总额在表格的第2行中。
- FALSE is used in the last argument, to find an exact match for the lookup value. 在最后一个参数中使用FALSE来查找查找值的精确匹配。
The formula in cell C7 is:
C7单元格中的公式为：
=HLOOKUP(B7,C2:F3,2,FALSE)
= HLOOKUP(B7，C2：F3,2，FALSE)
If the region name is not found in the first row of the lookup table, the HLOOKUP formula result is #N/A
如果在查找表的第一行中找不到区域名称，则HLOOKUP公式的结果为＃N / A
示例2：查找所选日期的费率 (Example 2: Find Rate for Selected Date)
Usually, an exact match is required when using HLOOKUP, but sometimes an approximate match works better. For example, if rates change at the start of each quarter, only those dates are entered as column headings.
通常，使用HLOOKUP时需要精确匹配，但有时近似匹配效果更好。 例如，如果费率在每个季度开始时发生变化，则仅将那些日期输入为列标题。
Then, with HLOOKUP and an approximate match, you can find the rate that was in effect for that date. In this example:
然后，使用HLOOKUP和近似匹配，您可以找到该日期的有效汇率。 在此示例中：
- a date is entered in cell C5 在单元格C5中输入日期
- the rate lookup table has two rows, and is in range C2:F3 汇率查询表有两行，范围为C2：F3
- the lookup table is sorted by the Date row, in ascending order 查找表按日期行按升序排序
- rate is in row 2 of the table. 费率在表格的第2行中。
- TRUE is used in the last argument, to find an approximate match for the lookup value. 在最后一个参数中使用TRUE，以查找查找值的近似匹配。
The formula in cell D5 is:
D5单元格中的公式为：
=HLOOKUP(C5,C2:F3,2,TRUE)
= HLOOKUP(C5，C2：F3,2，TRUE)
If the date is not found in the first row of the lookup table, the HLOOKUP formula result is the next largest value that is less than lookup_value.
如果在查找表的第一行中未找到日期，则HLOOKUP公式结果是小于lookup_value的下一个最大值。
The lookup value in this example is March 15th. That value is not in the date row, so the value for January 1st (0.25) is returned.
此示例中的查找值是3月15日。 该值不在日期行中，因此返回1月1日(0.25)的值。
下载HLOOKUP功能文件 (Download the HLOOKUP Function File)
To see the formulas used in today's examples, you can download the HLOOKUP function sample workbook. The file is zipped, and is in Excel 2007 file format.
要查看当前示例中使用的公式，可以下载HLOOKUP函数示例工作簿 。 该文件已压缩，并且为Excel 2007文件格式。
观看HLOOKUP视频 (Watch the HLOOKUP Video)
To see a demonstration of the examples in the HLOOKUP function sample workbook, you can watch this short Excel video tutorial.
要查看HLOOKUP函数示例工作簿中示例的演示，您可以观看此简短的Excel视频教程。
翻译自: https://contexturesblog.com/archives/2011/01/11/30-excel-functions-in-30-days-10-hlookup/
hlookup函数多个条件
782
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
