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.

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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!