Sumproduct to match values in different cells

**raj.bris** · 03-05-2013, 07:59 AM

Hi Experts

I am trying to use the following formula to match the values and get the result in cell N3.

Please Login or Register  to view this content.

Real values are in cells C87,C88,E87 and E88 and the anticipated values are in cells F3,H3,J3 and L3. The anticipated values can be in any order.

Please help me matching the anticipated values with real values.

Regards

**JBeaucaire** · 03-05-2013, 08:11 AM

Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook. Make sure the workbook demonstrates a manual mockup of your your desired results in cell N3, not a formula, just enter the expected results from the sample data shown.

**raj.bris** · 03-06-2013, 06:12 AM

File attached, real value cells are changed from the originally given.

thanks

**JBeaucaire** · 03-06-2013, 11:43 AM

This array formula entered into N3:

=--(SUM(COUNTIF($F$3:$M$3,$C$10:$E$11))=4)

...confirmed by pressing CTRL-SHIFT-ENTER to activate the array. 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.

**raj.bris** · 03-08-2013, 09:39 PM

Thanks Mate. Cannot believe that such big sumproduct formula can be shrunk to something like this small. Great work, it worked perfectly in one go.

Cheers