Formula_Find_Target

[josros60]

Hi,

I have the formula below works fine from this range(Range1) "A2:A10" but if I insert more rows formula won't work have not idea why won't work, any suggestion please.

formula:

=IF(ISNUMBER(MATCH(ROWS($1:1),IF(INDEX(MOD(INT((List2-1)/2^(TRANSPOSE(List1)-1)),2),MATCH(TRUE,MMULT(MOD(INT((List2-1)/2^(TRANSPOSE(List1)-1)),2),Range1)=$E$2,0),),TRANSPOSE(List1)),0)),"X","")

Thank you,

 

[KRice]

How many more rows are you inserting? You may be exceeding the limit of what MMULT can handle.

 

[josros60]

oh, need to insert 30 more rows.


thank you

 

[josros60]

Any help please.

 

[KRice]

Can you provide more details, please? I'm trying to understand what you want to do. What do the named ranges Range1, List1, and List2 refer to?

 

[josros60]

I would to inser 13 rows more instead of 17 rows be 30.
noticed also when enter target # and press enter really slow blue circle spinning.

Here the formulas: (also putting sample spredsheet:

List1 =

=ROW(INDIRECT("1:"&ROWS(Range1)))

List2 =

=ROW(INDIRECT("1:"&2^ROWS(Range1)))

Range1 =

=FIND_TARGET!$A$2:$A$17

Sheet:

MATCHING_TARGET_VALUE$.xlsm
	A	B	C	D
1	AMOUNTS	INVOICE #	RESULTS	$
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18	TOTAL OF SUM TARGET>>		$ -
FIND_TARGET

Cell Formulas
Range	Formula
D2:D17	D2	=IF(C2="X",A2,"")
C2:C17	C2	=IF(ISNUMBER(MATCH(ROWS($1:1),IF(INDEX(MOD(INT((List2-1)/2^(TRANSPOSE(List1)-1)),2),MATCH(TRUE,MMULT(MOD(INT((List2-1)/2^(TRANSPOSE(List1)-1)),2),Range1)=$E$2,0),),TRANSPOSE(List1)),0)),"X","")
C18	C18	=SUM(D2:D17)

Named Ranges
Name	Refers To	Cells
Range1	=FIND_TARGET!$A$2:$A$17	C2:C17, D2

thank you

 

[KRice]

You did not include column E, but based on your reference to a "target" and the $E$2 in the formula, I am guessing that it looks something like the example below. It would be helpful to describe what you are trying to do. I still have not completely figured out your formula, or why it is constructed the way it is. You have arrays that are 2^16 long and INDIRECT functions, both of which will slow down the sheet performance. It appears that you want to match an input "target" amount in $E$2 to the amounts shown in column A. And if a match is found, place an "X" in the corresponding row of column C (Results). And where there is an "X" in the Results column, copy the amount from column A into column D for summing. If this is true, then would not a much simpler formula work....like this?

Book2
	A	B	C	D	E
1	AMOUNTS	INVOICE #	RESULTS	$	Target
2	101				107
3	102
4	103
5	104
6	105
7	106
8	107		X	107
9	108
10	109
11	107		X	107
12	111
13	112
14	113
15	114
16	115
17	116
18	TOTAL OF SUM TARGET>>		214
FIND_TARGET

Cell Formulas
Range	Formula
C2:C17	C2	=IF($A$2:$A$17=$E$2,"X","")
D2:D17	D2	=IF($C$2:$C$17="X",$A$2:$A$17,"")
C18	C18	=SUM(D2#)
Dynamic array formulas.

 

[josros60]

make sense, thank you.

 

[KRice]

I'm happy to help. Note that this approach returns X's for all amounts that match the target input. I'm not sure if that is what you want...depends on your application/purpose..