Need help with using an array to lookup column matches

[lc130]

I'm trying to find duplicate payments from two accounts. Below is a screenshot of the spreadsheet. I'd like the formula to look for matching payments between columns C and F. When a match is found in column F, the formula pulls the associated values of columns A, B and C and places them next to the duplicate payment in column F. The duplicates may be in any row which makes me think I need an array formula.

I copied the desired result into columns G, H and I.

Any help is appreciated! I've also uploaded the spreadsheet.

Kelly
12-24-2015 8-40-18 AM.jpg

[newdoverman]

This will include duplicates of non-payments. Enter in G2 and fill across and down. Format column I as General.

Please Login or Register  to view this content.

This version of the formula will not include non-payment matches.

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[ben_hensel]

There's a way to do multi-element MATCH without array formulas just using SUMPRODUCT.

With a helper column:

Please Login or Register  to view this content.

Condensing the SUMPRODUCT into the INDEX is complicated though because INDEX defaults to returning the same row as the input cell when you feed it an array position of zero. (I thought it would throw an error you could trap with IFERROR but I guess not).


EDIT:
Wait, should it be checking the date or just the Account name & value? I assumed yes it should, but it looks like NDM's answer went the other way and didn't consider date.

[lc130]

Excellent! Thank you both for sharing your knowledge!

Kelly

[Czeslaw]

Please Login or Register  to view this content.