Match function not what I need

[Busymanjohn]

Hi, I am looking for a solution to a small problem. I have a range of cells ( Q14:AE14 ) that contain values varying from 1 to 5, in cell AF14 I want to return the cell that has a value =>3 ....... e.g. if the value 4 appears in R14, the result in AF14 would be 2 .... make sense?

 

[Abhijeet R. Joshi]

Hi John,

I am not fairly sure if I follow you, do you mean you want the counts in Cell AF14?
Or do you want to to get the position of the first number from the Col.Q?

 

[Jake Collins]

Dear Busymanjohn

=SMALL(($Q$14:$AE$14>=3)*COLUMN($Q$14:$AE$14),COUNTIF($Q$14:$AE$14,"<"&3)+1)-16 would seem to do the trick

This is an array formula so would need to be entered using Ctrl+shift+enter

 

[Busymanjohn]

Hi, in cell AF14 I want to show how many columns in from Q14 to AE14 that the value that is =>3 appears, so if cell R14 = 4 then the result in AF14 would be 2, because that is the 2nd column in from Q14. The values that are => 3 will only appear once in the range of Q14:AE14, so the function MATCH works when the value is 3, but I want something that looks for a value =>3 as in some cases, 3 will not appear, but 4 or 5 will.

 

[Busymanjohn]

Dear Busymanjohn

=SMALL(($Q$14:$AE$14>=3)*COLUMN($Q$14:$AE$14),COUNTIF($Q$14:$AE$14,"<"&3)+1)-16 would seem to do the trick

This is an array formula so would need to be entered using Ctrl+shift+enter

Hi Jake, this works fine, thanks for the reply, appreciate it