Index match problem omitting rows with no data.

Having Index - Match problems when a list of rows has 0 values.
VLookup has the same issues.
I've greatly simplified the actual formula.
I've looked at other posts but just can't get the concept.


Goal: Return value for this row # 5


Array

1 46
2 56
3 78
4 3
5 11
6 25
7 56
0
0
0
0

If I cheat and only consider the correct rows I get the right answer with this formula. INDEX(A1:B7,MATCH(5,A1:A7,0),2) = 11
In practice, i need to have extra rows.


My feeble attempt at a formula:

INDEX(A1:B11,MATCH(5,INDEX((A1:A11<>0)*(A1:A11),0),2) = #REF

Please help.

I don't think so. I did a lot of searching and rows with zeros screw the whole thing up. I tested the issue by eliminating the problematic rows and the formula you suggest - the one I originally used as well - worked. Unfortunately I need to provide space for additional future data. I'm going to try the moderator's suggestion and hope that the (C+S+E) step has to be only performed once. MSFT needs to improve this issue. Unfortunately, it sometimes takes them years to get a Round TUIT.