Help with formula for calculation and lookup

[Shodan909]

Hello,

I'm looking for help on a formula (array?) that will return a boolean value (in column I) where --The values in each row of E,F,G are compared individually to each row of A,B,C by subtracting each cell, and them summing the absolute value. Then, comparing the result to a range of numbers located in J1-J5.

For instance When E1-G1 is compared to A1-C9

47=SUM(ABS(SUM(E$1-A1))+ABS(SUM(F$1-B1))+ABS(SUM(G$1-C1)))
1=SUM(ABS(SUM(E$1-A2))+ABS(SUM(F$1-B2))+ABS(SUM(G$1-C2)))
62=SUM(ABS(SUM(E$1-A3))+ABS(SUM(F$1-B3))+ABS(SUM(G$1-C3)))
52=SUM(ABS(SUM(E$1-A4))+ABS(SUM(F$1-B4))+ABS(SUM(G$1-C4)))
64=SUM(ABS(SUM(E$1-A5))+ABS(SUM(F$1-B5))+ABS(SUM(G$1-C5)))
48=SUM(ABS(SUM(E$1-A6))+ABS(SUM(F$1-B6))+ABS(SUM(G$1-C6)))
57=SUM(ABS(SUM(E$1-A7))+ABS(SUM(F$1-B7))+ABS(SUM(G$1-C7)))
65=SUM(ABS(SUM(E$1-A8))+ABS(SUM(F$1-B8))+ABS(SUM(G$1-C8)))
70=SUM(ABS(SUM(E$1-A9))+ABS(SUM(F$1-B9))+ABS(SUM(G$1-C9)))

This would return TRUE because 47 and 1 reside in J1-J5.

E3-G3 returns false because the absolute sum does not match any in J1-J5.

Sorry if this isn't very clear. I don't know if this is something that can be accomplished in a one, or two part formula instead of code.

Columns A,B,C are actually over 1000 rows, and E,F,G are 100 rows.

Thanks!

 

[circledchicken]

Hello,

Here's one option to try. Enter this formula in I1 and drag down as far as necessary. (It is an array formula entered with CTRL-SHIFT-ENTER rather than just ENTER):

=IF(SUM(--(ISNUMBER((MATCH($J$1:$J$5,ABS($E1-$A$1:$A$9)+ABS($F1-$B$1:$B$9)+ABS($G1-$C$1:$C$9),0)))))>0,"Y","N")

You can change the 5 and 9 row references to whatever they are in your complete dataset.

• The part in bold returns the array of values we want to test against the numbers in column J.
• The MATCH function does this test, returning the rows where matches were found, with #N/A's otherwise.
• ISNUMBER converts the MATCH function to TRUE's and FALSE's.
• The -- converts the TRUE's and FALSE's to 1's and 0's.
• Finally, we SUM the result to see if any matches were found, and if so return a "Y".

 

[Shodan909]

Perfect! Exactly what I was looking for! Thanks alot circledchicken.
Your help is greatly appreciated.

Cheers.

 

[circledchicken]

Cool - your very welcome!