compare values from 2 columns and insert value from 3rd column

[joharmenezes]

I have attached an excel http://www.filedropper.com/book2_2 in which I have 2 sheets, master and data. I want to compare 2 columns, D & E of the data worksheet with 2 columns, A & B of the master WS and insert values from the C column in the target column if both columns match. I tried sumproduct but since there are duplicate rows in the master worksheet it seems to sum up the values in the C column. I cannot delete the duplicate rows in the master WS. I know this can be resolved using the index & match fuctions but I can't seem to get the logic. Can someone please help me? Thanks

 

[Hui]

IronMan


in Data!C2 put

=INDEX(Master!C$2:C$11778,MATCH(Data!A2&Data!B2,Master!A$2:A$11778&Master!B$2:B$11778,0))

Ctrl+Shift+Enter

Then copy

Paste down to bottom of Column C

 

[joharmenezes]

Hi Hui, thanks a lot. but can u please explain the logic behind this or can you direct me to a web page explaining it?

 

[Hui]

=INDEX(Master!C$2:C$11778,MATCH(Data!A2&Data!B2,Master!A$2:A$11778&Master!B$2:B$11778,0))


This is a classical Index/Match combination except that it is using two Concatenated fields instead of a single field


[code]MATCH(Data!A2&Data!B2,Master!A$2:A$11778&Master!B$2:B$11778,0)

Match looks up the exact match of the value of Data!A2&Data!B2 from the area Master!A$2:A$11778&Master!B$2:B$11778

Match will return the index number or position of the match in that range


=INDEX(Master!C$2:C$11778,Index Number from above)[/code]


Index is a lookup function that looks up the range Master!C$2:C$11778 and retrieves the value from position "Index Number" in that range


I hope that helps some little bit


As a final comment, never use Entire Columns as ranges in Index/Match lookups, it can have odd effects

 

[joharmenezes]

thanks Hui. great explanation. but why do we have to convert it to an array formula? why doesn't it work normally?