If two cells match, return value from third

mn8809 picture mn8809 · Oct 15, 2014 · Viewed 499k times · Source

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.

Answer

pnuts picture pnuts · Oct 15, 2014

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.