# Thread: Match columns A,B in two sheets and Bring ColumC data

1. ## Match columns A,B in two sheets and Bring ColumC data

Hello All,

I have two worksheet having (Match1 ) Sheet and (Match2) sheet . I require match1 sheet column AB with Match2 sheet if matched then bring column C of Match2 sheet to Column C in Match1 sheet. Sample attached.

2. Is this what you want? This won't be correct if there are multiple matches, however.

=SUMPRODUCT((A3=Match2!\$B\$2:\$B\$177)*(B3=Match2!\$A\$ 2:\$A\$177)*Match2!\$C\$2:\$C\$177)

3. Hi Farrukh

See attached file for one method of returning data based on matching the lookup entries in two columns.
I combine the two columns into one lookup code on each sheet, in an adjacent spare column.
Then, using this combined lookup code, return the row number that matches this combined lookup-code.
If the match isn't found, the formula shows this, otherwise it brings the corresponding value from the desired column.

Is this what you wanted???

zeddy

4. An alternate way is to use in C3:
=IFERROR(AVERAGEIFS(Match2!\$C\$2:\$C\$177,Match2!\$A\$2 :\$A\$177,B3,Match2!\$B\$2:\$B\$177,A3),"")

and copy it down the column. If more than 1 value it will be averaged, if no values or an error, it will be a null string.

Steve

5. A similar methodolgy to Zeddy's (but without the intermediate columns) is to use the array formula (confirm with ctrl-shift-enter) in C3:
=IFERROR(INDEX(Match2!\$C\$2:\$C\$177,MATCH(B3&";"&A3, Match2!\$A\$2:\$A\$177&";"&Match2!\$B\$2:\$B\$177,0)),"")

and copy it down the column. With this formula, if there are more than 1 entry the first entry found will be obtained instead of the average of all of them.

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•