Forum Discussion

Advanceacount's avatar
Advanceacount
Copper Contributor
Jun 15, 2022

New Formula

I need a new formula but i don't know how to do it. I'll illustrate the condition with an example.

 

I have value in all 170 cells, then i give another value in another cell below it, be the value 68159 in cells E189. So now i want a formula which can tell me if adding any two/three of 170 cells will equal to the value of cell E189.

Formulas and Functions
office 365
  • mtarler's avatar
    mtarler
    Silver Contributor
    Jun 15, 2022
    Here is a formula (assuming you have Excel 365) that you can try:
    =LET(in,A1:A170,target,B1,diff1,target-in,double,MAX(IFERROR(MATCH(in,diff1,0),0)),diff2,diff1-in,IF(double,double,MAX(IFERROR(MATCH(in,diff2,0),0))))
    Change the "in" from A1:A170 to the range of inputs and the "target" from B1 to your target value (is that E189?). The output will be 0 if not found or a number indicating one of the values in the doublet or triplet that was found. NOTE: it CAN use the same value multiple times so if the target is 20 and you have the numbers 10,11,12,13 it will find 10 because 10+10 is 20.

Resources