 # Identify First number in row that is less than or equal to x

• Does anyone know a good formula for identifying the first value in a row that is less or equal to than another?

For example, if I have the row:

35 34.2 33.9 33.8 33.9 33.6 33.8 33.9 34 35 36 37

I would like to identify the first number from the left that is less than or equal to 34 i.e. 33.9 in position 3.

Thanks

• Re: Identify First number in row that is less than or equal to x

I thought I could use =MAX(A1:L1,COUNTIF(A1:L1,"<=34")) to give me the highest value from the left hand side that is less than or equal to 34 but this doesn't seem to be working.

• Re: Identify First number in row that is less than or equal to x

Thanks,

That works well, but the only thing is that the '=' overrides the '<', so that when I apply it to the above formula it gives me 34 (in position 9) rather than 33.9 (in position 3). I've tried adding " " and ( ) around the <=34 to isolate it, but to no avail. Any suggestions?

• Re: Identify First number in row that is less than or equal to x

I want the first number in the row that is less than or equal to 34. =LARGE((A1:L1<=34)*(A1:L1),1) gives me the first number equal to 34 (i.e. it ignores the <) also I just realised that this function returns the largest number less than 34, not the first number less that 34.

• Re: Identify First number in row that is less than or equal to x

I think the second function is better, it still has the same problem of ignoring the < as soon as I put in = i.e. =INDEX(MAX((A1:L1<=34)*(A1:L1)),1)

• Re: Identify First number in row that is less than or equal to x

The first number from the left that is less that or equal to 34.

• Re: Identify First number in row that is less than or equal to x

Only I need it to identify the first number from the left that is less than 34. i.e. the 33.9 in the srd column, not the 33.9 in the 8th column...

• Re: Identify First number in row that is less than or equal to x

=INDEX(A1:L1,MATCH(TRUE,A1:L1<=34,0))
Confirm Contrl+Shift+Enter

• Re: Identify First number in row that is less than or equal to x

Great thanks, that works well. Can the ADDRESS function just be applied to this to find the coordinates of the cell? I tried =ADDRESS(INDEX(A1:L2,MATCH(TRUE,A1:L1<=34,0))),A1:L1,4) but there are 'not enough arguments'?

• Re: Identify First number in row that is less than or equal to x

I also tried =ADDRESS(ROW(INDEX(B2:AR2,MATCH(TRUE,B2:AR2<=34,0))),COLUMN(B2:AR2),4) with control + Shift + Enter, this results in the row, but does not give the correct column. i.e. it just gives A1, A2, A3, A4 etc

• Re: Identify First number in row that is less than or equal to x

This is just giving me #VALUE!, what is the "a1:" for?

• Re: Identify First number in row that is less than or equal to x

Yes the first number less than 34. This function was able to do it: =INDEX(A1:L1,MATCH(TRUE,A1:L1<=34,0)) which gives the numerical value, so now I just need to find the coordinates for the numerical value I've found

• Re: Identify First number in row that is less than or equal to x

For the first less than 34 from left
=INDEX(A1:L1,MATCH(TRUE,A1:L1<=34,0))
For the first large which is less than 34 from left
=INDEX(A1:L1,MATCH(MAX(IF(A1:L1<34,A1:L1)),A1:L1,0))