将一张纸中的两列匹配到另一张纸中的两列 [英] Matching two columns in one sheet to two columns in another

问题描述

我需要基于两个工作表之间的VLOOKUP查询将状态分配给一行。问题在于标识符并不总是唯一的。但是，标识符+日期值应该是唯一的。我想使用：

I need to assign a status to a row based on a VLOOKUP query between two worksheets. The problem is that the identifier is not always unique. However, the identifier + a date value should be unique. I wanted to use:

=VLOOKUP(A3&H3,'OtherSheet'!D:E,1,FALSE)  

其中A3为标识符，H3为对应的日期。另一张纸上的D是标识符，E是日期列。但是，我一直得到＃N / A。

with A3 being the identifier and H3 being the corresponding date. D in the other sheet is the identifier and E is the date column. However, I keep getting #N/A.

这是否意味着与 identifier + date不匹配，还是Excel在D列或E列中查找 identifier + date？如果后者是正确的，那么在匹配搜索模式时如何让Excel将D和E连接起来？

Does this mean that there are no matches with the "identifier+date" or is Excel looking for "identifier+date" in either column D or E? If the latter is true, how can I let Excel concatenate D and E when matching to the search pattern?

推荐答案

使用此公式将其他工作表的D列中的A3和其他工作表的E列中的日期与H3匹配。

Use this formula that will match A3 in D column of othersheet and H3 with the date in column E of the othersheet.

=INDEX(OtherSheet!F:F,MATCH(1,INDEX((OtherSheet!D:D=A3)*(OtherSheet!E:E=H3),),0))

该公式将从OtherSheet的F列返回数据。
您可以根据需要修改范围 OtherSheet！F：F 。

The formula will return data from F column of OtherSheet. You can modify the range OtherSheet!F:F as appropriate.

这篇关于将一张纸中的两列匹配到另一张纸中的两列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！