I have a formula which returns the first non-zero value in a row - the numbers will fill up as the year goes on.
1 2 3 _ _ _
=IFERROR(LOOKUP(1E+100,H21:S21),"")
How do I find the cell reference of this value the LOOKUP returns - without using MATCH as the value isn't necessarily unique? What I want is the same cell but 51 rows down, or so. Thanks.
Match() is probably the right way to go.
=OFFSET(H21, 50, MATCH(1E+100,H21:S21,-1)-1)
The last parameter of Match(), when set to -1 says to find the first value in the range (H21:S21) that is greater than the lookup value (1E+100), which is what I think you are doing here with LOOKUP
The Offset() goes down 50 rows and then we just subtract 1 from the result of Match to determine how many columns to move across.
If you just want the address of the item found by Match() you could use:
=ADDRESS(21, MATCH(10000,H21:S21,-1))
But I would imagine that Offset() probably gets you closer to your end game.
