CODEWITHSUNDEEP
excelexcel-formula
Jain
Jain

Reputation: 999

Looping If condition in formula

Spreadsheet Hi, I want to find the value of column A when the Value of E first goes to zero. In above table the value of A column would be 47 as it is the first instance when the E value in same row is 0. I tried the IF function

=IF(AND(E1:E100=0,E2:E99<>0),A1:A100,0)

But this doesnt work. I cannot find out on how to loop through each row or use any index.

Edits:

  1. I am looking for an excel way to do it and not using VBA.
  2. I am inserting the value at the bottom of the table which shows the value of A where does E becomes zero

Upvotes: 0

Views: 77

Answers (1)

user4039065
user4039065

Reputation:

A simple INDEX(MATCH(...)) pair should take care of this handily.

=IFERROR(INDEX(A:A, MATCH(0, E:E, 0)), "no zero found in E")

I've used full column references but I don't think that should adversely affect performance on the lookup unless many lookups of this nature were used and matches were not found.

Upvotes: 2

Related Questions