Excel VBA在其他工作表中查找值并复制它们 [英] Excel VBA look for values in other sheet and copy them

问题描述

我有两个Excel表：Sheet1和Sheet2。

I have two excel sheets: "Sheet1" and "Sheet2".

Sheet1包含3列，N列。
示例：

Sheet1 contains 3 columns with an N number of rows. Example:

x     y     result

A     b
B     m
L     a
A     b
B     b

Sheet2包含3列但结果作为每个x和y组合的解释。

Sheet2 contains 3 columns as well but with the result as an explanation for each x and y combination.

示例：

x     y      result

A     a        1
A     b        2
A     c        3
B     a        4

请注意，A！= a，结果并不总是数值。

Please note that A != a, and result is not always a numeric value.

因此，我基本上需要从Sheet1的值中搜索Sheet2给定的组合，并将结果从Sheet2复制到Sheet1。

我的VBA代码示例如何实现呢？
也许使用Excel公式甚至可能？可能是INDEX和MATCH？

Can you give me example of VBA code how to achieve this? Probably It's even possible with an Excel formula? Probably INDEX and MATCH? Anyhow, I can't figure this out by myself.

感谢

推荐答案

您可以使用公式本身。在您的sheet1中，请在C2单元格中粘贴以下公式。

You can do this using formula itself. In your sheet1, please paste the below formula in C2 cell.

=IF(SUMPRODUCT((--EXACT(Sheet2!A:A,A2))*(--EXACT(Sheet2!B:B,B2))*(--(Sheet2!A:A<>""))*(--(Sheet2!B:B<>"")),ROW(Sheet2!A:A))=0,"",INDEX(Sheet2!C:C,SUMPRODUCT((--EXACT(Sheet2!A:A,A2))*(--EXACT(Sheet2!B:B,B2))*(--(Sheet2!A:A<>""))*(--(Sheet2!B:B<>"")),ROW(Sheet2!A:A))))

并将其复制到其他单元格。

and copy the same to other cells. It would work.

请检查以下图片：

这篇关于Excel VBA在其他工作表中查找值并复制它们的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！