Copying values from matched rows

[seaweasel]

I'm working on a project requiring the matching of data from multiple worksheets and got some useful advice from someone on here a few months ago to get it up and running.

At the moment I'm using SUMPRODUCT with an IF argument to check rows on one sheet for matches on 5 others and return a "Yes" or "No" value, which is working fine. The next bit of analysis however requires copying the value from another column on rows that match, and pasting it into the first sheet.

=IF(SUMPRODUCT(--('Sheet2'!$B$2:$B$108=C2),--('Sheet2'!$I$2:$I$108=J2))>=1,"Yes","No")

What I need to insert into the formula above is something like:

THEN copy $Level from CurrentRowSheet2, paste $Level CurrentRowSheet1

Hope that makes sense! I'd appreciate any advice as to whether this functionality can be shoehorned into what I have, or if something like INDEX/MATCH might be more appropriate.

If any more details are required, please let me know, I'd be grateful for any help.

[NBVC]

A visual sample (like a sample spreadsheet) may help to see what is the best option...

[seaweasel]

I've attached a sample sheet with a working version of the formula I've used so far.

[NBVC]

Try:

=INDEX(Sheet2!C$2:C$8,MATCH(1,(Sheet2!$A$2:$A$8=$A2)*(Sheet2!$B$2:$B$8=$B2),0))

confirmed with CTRL+SHIFT+ENTER not just ENTER... and copied down and across...

Non-matches will return #N/A

or try

=INDEX(Sheet2!C$2:C$8,MATCH(1,INDEX((Sheet2!$A$2:$A$8=$A2)*(Sheet2!$B$2:$B$8=$B2),0),0))

Confirmed with ENTER only...

[contaminated]

NBVC,
by some reasons when I try your formula I get Non-Advanced in all cells?!

[NBVC]

Which formula? I tested both and got expected results...

The 1st formula requires CSE confirmation!

[contaminated]

nevermid
i'm tired a bit today
sorry

[contaminated]

nbvs
CAN U POST WORKBOOK?

[NBVC]

It's NBVC, by the way...

See attached...

[contaminated]

NBVC,
So why when I trying your formula I get "Non-Advanced" in all cells. I really dont know why?!
Thats why a was amazed when u told me that you're getting expected results...

[contaminated]

maybe something is unticked in options...

[contaminated]

Yep
I had manual calculations instead od automatic...

[seaweasel]

Thanks NBVC, worked a treat!