比较两列，并在Excel中返回特定的相邻单元格 [英] Comparing two columns, and returning a specific adjacent cell in Excel

问题描述

我正在使用的组合，如果， vlookup ， match ， iserror 函数，不幸的是我找不到正确的公式。

I am using a combination of if, vlookup, match, iserror functions, and unfortunately I've not been able to find the right formula.

两列比赛很容易。一旦找到匹配，艰难的部分就返回了一个特定的单元格。

Comparing two columns for matches is easy enough. the tough part has been returning a specific cell once a match is found.

所以我正在处理的东西是这样的：

So what I'm dealing with is something kind of like this:

Header     Column A   Column B   Column C  Column D
Row 1      111        AAA        112
Row 2      222        BBB        111
Row 3      333        CCC        221
Row 4      444        DDD        333

我试图匹配列值在列A 中，与列C 。所以如果有匹配，我想在列B 中的相应值填充列D 。不是一个很好的解释，而是允许我直观地向你展示我正在寻找什么

I'm trying to match column values in Column A, with Column C. So if there's match, I want the corresponding value in Column B to populate in Column D. Not a great explanation, but allow me to visually show you what I'm looking for

Header     Column A   Column B   Column C  Column D
Row 2      111        AAA        112
Row 3      222        BBB        111       AAA
Row 4      333        CCC        221
Row 5      444        DDD        333       CCC

由于单元格 A1 匹配单元格 C3 ，我想要 D 返回 B2

Since Cells A1 matches cell C3, I want D to return B2

与行 5 。由于 A4 和 C5 匹配，我想要的值为 B5

Same with Row 5. Since A4 and C5 match, I want the value for B5

让我知道这是否有意义，或者需要进一步澄清。

Let me know if this makes sense or if you need further clarification.

推荐答案

与这个问题非常相似，我建议在D列中使用相同的公式，尽管有一些修改范围：

Very similar to this question, and I would suggest the same formula in column D, albeit a few changes to the ranges:

=IFERROR(VLOOKUP(C1, A:B, 2, 0), "")

如果要使用匹配，则必须使用 INDEX 同样如此：

If you wanted to use match, you'd have to use INDEX as well, like so:

=IFERROR(INDEX(B:B, MATCH(C1, A:A, 0)), "")

但这对我来说真的很冗长，你需要知道如何正确使用两个功能（或三个，如果你不知道如何 IFERROR 工作）！

but this is really lengthy to me and you need to know how to properly use two functions (or three, if you don't know how IFERROR works)!

注意： code> = IFERROR（）可以代替 = IF（）和 = ISERROR（）在某些情况下：）

Note: =IFERROR() can be a substitute of =IF() and =ISERROR() in some cases :)

这篇关于比较两列，并在Excel中返回特定的相邻单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！