CODEWITHSUNDEEP
excel
biodiverse
biodiverse

Reputation: 13

excel if cell contains then matching value then copy adjacent cell

I am looking for a simple IF statement that will allow me to compare two columns, and if they match, to copy the value from a third column into a new cell.

EG: Columns 1 and 3 contain names with column 2 containing value for the column 1 name. If column 3 name matches column 1 name I would like the associated value from column 2 to be pasted into a new cell. 

Column 1    Column2 Column3
ACAN        7       ADNP
ADNP        4       AHR  
AMER        3       ACAN

Result I would like:

Column 1    Column2  Column3 Column 4    
ACAN         7       ADNP     4    
ADNP         4       AHR      0    
AMER         3       ACAN     7

I have the following formula working: 

if(isnumber(D1:KY1=B:B,A:A,D3)

but this just takes the first value found in column A - not the value I need depending on the cell with the correct name.

Thank you in advance!

Upvotes: 1

Views: 28944

Answers (1)

ImaginaryHuman072889
ImaginaryHuman072889

Reputation: 5185

Type the following formula into cell D1 and drag down:

= IFERROR(INDEX($B$1:$B$3,MATCH(C1,$A$1:$A$3,0)),0)

The IFERROR is used to populate the cell with 0 if no match is found.

enter image description here

Upvotes: 5

Related Questions