vba Excel，在同一行的两列中搜索不同的值，从该行的单元格返回结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18880744/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel, Search two columns for different values on the same row, return results from a cell in that row
提问by Coelus
I'm trying to make a search through a data sheet to fill in cells that will be printed as a chart.
我正在尝试通过数据表进行搜索以填充将作为图表打印的单元格。
The search will need to match two criteria that are on the same row, then return the value from another cell in that same row. It will look for a week# and location on the same row, then return a score that is several columns down on that row.
搜索将需要匹配同一行上的两个条件，然后从同一行中的另一个单元格返回值。它将在同一行上查找 week# 和 location，然后返回该行向下几列的分数。
My intention was to use a a couple LOOKUP with an AND statement to build some kind of megafunction. Like IF(AND(VLOOKUP($H$27,B:B,"",FALSE)<>(VLOOKUP($G$26,C:C,42,FALSE))) I would need both of the search criteria to match before it returned the value from column 42.
我的意图是使用带有 AND 语句的一对 LOOKUP 来构建某种宏功能。像 IF(AND(VLOOKUP($H$27,B:B,"",FALSE)<>(VLOOKUP($G$26,C:C,42,FALSE))) 我需要两个搜索条件才能匹配它返回了第 42 列的值。
Week# and score for the X&Y of the chart. You would input the location in a cell($G$26) for the search and it would populate the cells for the chart. Each reference cell in the chart would use the formula with the only difference being what week it looks at.
Week# 和图表 X&Y 的分数。您将在单元格 ($G$26) 中输入位置进行搜索，它会填充图表的单元格。图表中的每个参考单元格都将使用该公式，唯一的区别是它查看的是哪一周。
I willing to accept that I may be going about this in a terrible way.
我愿意接受我可能会以一种可怕的方式去做这件事。
回答by pnuts
The syntax for VLOOKUPis VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) where lookup_value must be a single criterion. The simplest solution to look up a result based on multiple criteria can be to convert multiple criteria into a single criterion, with concatenation. Instead of trying to find a match for “John where also Smith”, string the two together (eg =A1&B1) and seek to match “John Smith”. To avoid corrupting the source data this usually means adding a helper column to contain =A1&B1 (or a helper row in case of HLOOKUP and say =A1&A2). The table_array may also require an additional column (or row) to hold values such as "John Smith".
VLOOKUP的语法是 VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) 其中 lookup_value 必须是单个标准。根据多个标准查找结果的最简单解决方案是将多个标准转换为单个标准，并进行串联。不要试图找到“John where also Smith”的匹配项，而是将两者串在一起（例如 =A1&B1）并寻求匹配“John Smith”。为了避免破坏源数据，这通常意味着添加一个辅助列来包含 =A1&B1（或者在 HLOOKUP 的情况下添加一个辅助行并说 =A1&A2）。table_array 可能还需要一个额外的列（或行）来保存诸如“John Smith”之类的值。
回答by ElectricJoe
See if the explanation at this link is of any help: http://spreadsheets.about.com/od/lookupfunction1/ss/2011-03-03-excel-2010-vlookup-multiple-values-sbs-tutorial_10.htm.
看看这个链接的解释是否有帮助：http: //spreadsheets.about.com/od/lookupfunction1/ss/2011-03-03-excel-2010-vlookup-multiple-values-sbs-tutorial_10.htm。
According to that explanation your formula should be something like
=INDEX($A:$AP,MATCH($H$27&$G$26,$B:$B&$C:$C,0),42)
根据那个解释，你的公式应该是这样的
=INDEX($A:$AP,MATCH($H$27&$G$26,$B:$B&$C:$C,0),42)
After you enter that formula - just before you hit the Enter key - press Ctrl-Shift-Enter to make this an array formula. The link explains it more in detail when checking all intermediate steps.
输入该公式后 - 就在您按下 Enter 键之前 - 按 Ctrl-Shift-Enter 使其成为数组公式。该链接在检查所有中间步骤时更详细地解释了它。
