INDEX MATCH help

[excelos]

hello,

I want to use the index-match functions and would like some help.

I wrote this function:

=INDEX($C$212:$AM$212,212,MATCH(>0,$C$212:$AM$212,0))

I want it to search in the range $C$212:$AM$212 and return the location of the first non zero cell.

However, it does not seem to accept the >0 in the value field.

Any idea?

Apart from that, is the function okay?

Thanks!

 

[WaterGypsy]

If you are looking for the first numeric value then use MATCH(0,$C$212:$AM$212,-1), the -1 looks for a value greater than the search term

The 212 in the INDEX should be 1 as it is referring to the row in the array defined by $C$212:$AM$212

 

[excelos]

If you are looking for the first numeric value then use MATCH(0,$C$212:$AM$212,-1), the -1 looks for a value greater than the search term

The 212 in the INDEX should be 1 as it is referring to the row in the array defined by $C$212:$AM$212

Hello,

Following your instructions, I wrote this:

INDEX($C$212:$AM$212,1,MATCH(1,$C$212:$AM$212,-1))

I put MATCH(1 and not MATCH(0 because the -1 at the end searches for greater or EQUAL, while I want only greater than zero.

However, I think it works in the reverse, it returns the value of the cell that is the first non zero, but from the end of the range and not the beginning.

Any idea?

It seems your advice only works when the array is appropriately sorted, which is not the case in my array.

 

[AhoyNC]

This will give the position of the first column greater than 0.

Excel Workbook
	C	D	E	F	G	H
210	4
211
212	0		0	6	7	0
Sheet

 

[WaterGypsy]

Sorry .... I hadn't read the info on Match correctly ... it returns the lowest value greater than the requested parameter and the values need to be in order which is no good for you I am afraid