Forum Discussion
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 ...
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.
=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.