CODEWITHSUNDEEP
arraysexcel
user50210
user50210

Reputation: 113

Excel Array Formula If Statement that Returns the Value that Matches a Condition

I have an array formula in excel of this form:

=IF(ABS(range-0.25)<0.1,1,0)

Which basically looks through the entire row defined as range for a value within 0.1 of 0.25. If it finds something that meets this condition, it just prints the number 1.

What I would like it to do is print the index in range at which the value that matches this condition is found. So for example, if the 11th value in range is .2505 (which matches my condition), I would like the output to be 11. Is there any way to do this?

Upvotes: 0

Views: 1379

Answers (1)

tigeravatar
tigeravatar

Reputation: 26660

Formula solution, does not require array entry:

=MATCH(TRUE,INDEX(ABS(range-0.25)<0.1,),0)

Note that it will return an error if nothing matches your condition. You can avoid that by wrapping it in IFERROR

Upvotes: 2

Related Questions