Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

If two cells match, return value from third

Tags:

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.

like image 811
mn8809 Avatar asked Oct 15 '14 02:10

mn8809


People also ask

How do you return a value when a cell matches another cell?

The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range. For example, if the range A1:A3 contains the values 5, 25, and 38, then the formula =MATCH(25,A1:A3,0) returns the number 2, because 25 is the second item in the range.

How do you compare two cells and return YES if they are matched in Excel?

The following formula can help you quickly compare two cells in a row and return Yes if they are matched in Excel. Please do as follows. 1. Select a blank cell, copy formula =IF(A2=B2,"Yes","") into the formula bar and then press the Enter key.


2 Answers

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.

like image 81
pnuts Avatar answered Nov 03 '22 21:11

pnuts


=IF(ISNA(INDEX(B:B,MATCH(C2,A:A,0))),"",INDEX(B:B,MATCH(C2,A:A,0)))

Will return the answer you want and also remove the #N/A result that would appear if you couldn't find a result due to it not appearing in your lookup list.

Ross

like image 43
Ross Avatar answered Nov 03 '22 20:11

Ross