Excel，在同一行搜索不同值的两列，从该行中的单元格返回结果 [英] Excel, Search two columns for different values on the same row, return results from a cell in that row

问题描述

我想通过数据表进行搜索，以填充将打印为图表的单元格。

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.

我打算使用一对夫妇LOOKUP使用AND语句来构建某种宏功能。像IF（AND（VLOOKUP（$ H $ 27，B：B，，FALSE）（VLOOKUP（$ G $ 26，C：C，42，FALSE）））我需要两个搜索条件匹配之前从第42列返回值。

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.

图表的X& Y的周数和分数，您将在单元格中输入位置（$ G $ 26）对于搜索，它将填充图表的单元格。图表中的每个参考单元格将使用公式，唯一的区别是它看起来的星期。

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.

I愿意接受，我可能会以这种可怕的方式来解决这个问题。

I willing to accept that I may be going about this in a terrible way.

推荐答案

看看这个链接的解释是否是帮助：
http://spreadsheets.about.com/od/lookupfunction1/ss/2011-03-03-excel-2010-vlookup-multiple-values-sbs-tutorial_10.htm

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.

根据这个解释，你的公式应该是一些东西ke
= INDEX（$ A：$ AP，MATCH（$ H $ 27& $ G $ 26，$ B：$ B& $ C：$ C，0），42） code>

According to that explanation your formula should be something like =INDEX($A:$AP,MATCH($H$27&$G$26,$B:$B&$C:$C,0),42)

输入该公式后 - 在您按Enter键之前 - 按Ctrl-Shift-Enter使其成为数组公式。检查所有中间步骤时，该链接将详细说明。

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.

这篇关于Excel，在同一行搜索不同值的两列，从该行中的单元格返回结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！