XMatch Not Returning Expected Result

[Jeffrey Mahoney]

I'm a little frustrated with this because I expected to finish an hour ago. So you get my rant.

I have some costs in line with some cells Containing TRUE so I can Use SumIFS to sum the values, but not the summary values for each year. As you can see cell U8 is not TRUE and is a summary column

Now I want to find the first cost in a row that is over zero. I'm using xmatch like this in cell I17:
=XMATCH(0.00001,$I$7:$ET$7*$I$8:$ET$8,1,1)
or
=XMATCH(1e-99,$I$7:$ET$7*$I$8:$ET$8,1,1)
But as you can tell, it returns 17 instead of 12. Cell T7 is definitely a value, not text. Cell T9 shows the same value; it is a formula: =T8*T7

Please take a look

Jeff

ProjectsRegister - Support_4.xlsm
	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
5	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19
6	Jan-14	Feb-14	Mar-14	Apr-14	May-14	Jun-14	Jul-14	Aug-14	Sep-14	Oct-14	Nov-14	Dec-14	2014	Jan-15	Feb-15	Mar-15	Apr-15	May-15	Jun-15
7	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	6,256,117.53	6,256,117.53	0.00	0.00	0.00	94.00	0.00	36,082.00
8	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	0	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE
9	0	0	0	0	0	0	0	0	0	0	0	6256117.53	0	0	0	0	94	0	36082
10
11	17
Last EAC

Cell Formulas
Range	Formula
I6:EU6	I6	=SAPHistoryTbl[[#Headers],[Jan-14]:[2024]]
I9:AA9	I9	=I8*I7
I11	I11	=XMATCH(0.00001,$I$7:$ET$7*$I$8:$ET$8,1,1)
Dynamic array formulas.

 

[Fluff]

It's returning 17 as that is the nearest value to .00001.
How about

=TAKE(FILTER(SEQUENCE(,142),$I$7:$ET$7*$I$8:$ET$8>0),,1)

 

[JamesCanale]

MrExcelPlayground13.xlsx
	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
5	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19
6
7	0	0	0	0	0	0	0	0	0	0	0	6256118	6256118	0	0	0	94	0	36082
8	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	0	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE
9	0	0	0	0	0	0	0	0	0	0	0	6256118	0	0	0	0	94	0	36082
10
11	17
12	12
Sheet25

Cell Formulas
Range	Formula
I9:AA9	I9	=I8*I7
I11	I11	=XMATCH(0.00001,$I$7:$ET$7*(--($I$8:$ET$8)),1)
I12	I12	=MIN(FILTER(SEQUENCE(1,COLUMNS(I7:AA8))*(I7:AA7*I8:AA8>0),SEQUENCE(1,COLUMNS(I7:AA8))*(I7:AA7*I8:AA8>0)>0))

 

[Jeffrey Mahoney]

It's returning 17 as that is the nearest value to .00001.
How about

=TAKE(FILTER(SEQUENCE(,142),$I$7:$ET$7*$I$8:$ET$8>0),,1)

I haven't worked with TAKE yet, but now it's worth a look. I seen what you did with the sequence to return the series. I removed that and just used the date range above to return the correct date, which was what I wanted the final result to be anyway
=TAKE(FILTER(I6:ET6,$I$7:$ET$7*$I$8:$ET$8>0),,1)

Thank you

 

[Fluff]

Glad we could help & thanks for the feedback.