如果两个单元格匹配，返回值从第三个 [英] If two cells match, return value from third

问题描述

列A：2300个订单号的列表

列B：电子邮件与订单号相关联的地址

列C：我需要电子邮件地址的100个特定订单号的列表

所以，我在看搜索列A与C匹配的值，并从新列（D）中的列B返回电子邮件地址。

当前公式几乎可以正常工作，但不是返回匹配C的电子邮件地址，它会从同一行返回电子邮件地址。

  = IF（ISERROR（MATCH（C2，A：A，0）），B2）
  

本质上我只需要上面的公式中的B2就可以从匹配的相同行返回值。

解决方案

我想你想要的是：

  = INDEX（B：B， MATCH（C2，A：A，0））
  

我应该提到 MATCH 检查位置在A：A（给定 0 或FALSE，参数）中可以找到该值，它只会查找完全匹配并给出其性质，只有第一个实例发现）然后 INDEX 返回B：B中该位置的值。

Here's a simple explanation of what I'm having trouble with.

Column A: List of 2300 order numbers
Column B: Email Address associated with an order number
Column C: List of 100 specific order numbers that I need the email address for

So, I'm looking to search column A for a value that matches C, and return the email address from column B in a new column (D).

The current formula almost works, but instead of returning the email address where A matched C, it returns the email address from the same row.

=IF(ISERROR(MATCH(C2,A:A,0)),B2)    

Essentially I just need B2 in the formula above to return the value from the same line that matched.

解决方案

I think what you want is something like:

=INDEX(B:B,MATCH(C2,A:A,0))  

I should mention that MATCH checks the position at which the value can be found within A:A (given the 0, or FALSE, parameter, it looks only for an exact match and given its nature, only the first instance found) then INDEX returns the value at that position within B:B.

这篇关于如果两个单元格匹配，返回值从第三个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！