Need help with a formula

[BerrSD]

I have this formula: =INDEX(L11:L131,MATCH(99999,L11:L131))
It was working and now it doesn't. Cannot figure out why. I need help.
It's supposed to work like this:
There is an array, for example B1:B4 and a cell A1

Initially;

B1 = Null A1 = Null
B2 = Null
B3 = Null
B4 = Null

First Pass

B1 = 20 A1 = 20
B2 = Null
B3 = Null
B4 = Null

Second Pass

B1 = 20 A1 = 4
B2 = 4
B3 = Null
B4 = Null

Third Pass

B1 = 20 A1 = 31
B2 = 4
B3 = 31
B4 = Null

Fourth Pass

B1 = 20 A1 = 66
B2 = 4
B3 = 31
B4 = 66


My problem is that A1 displays 0 and it shouldn't. There is something
missing in the formula but I don't know what. I reposted this before but my
former post is no longer here. Should've printed it out.
Any help would be greatly appreciated. Thanks.

Bernard

 

[Don Guillett]

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

 

[BerrSD]

If desired, send your file to my address below. I will only look
if: 1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

I cannot send the file. It's confidential and proprietary. You gave me the
forula and as I said, it worked and then stopped. I tried finding my former
post so that I could go over it again but it's no longer here. What
happened to it?

Bernard

 

[Don Guillett]

I never gave you anything and don't care about your data. Surely you can
provide "DUMMY" data. Perhaps it is as simple as you are using a formula for
column L when you want another column. If so, you must have attended Texas A
& M.

 

[Bob Phillips]

Why aren't you using?

=INDEX(B1:B4,MATCH(99999,B1:B4))

 

[BerrSD]

=INDEX(B1:B4,MATCH(99999,B1:B4))

INDEX(L11:L131,MATCH(99999,L11:L131))

Same formula except one works the other doesn't. Got the second formula to
work on a new worksheet but not on my sheet.


Bernard

 

[Bob Phillips]

Not if the data is in B1:B4 as you showed.

 

[BerrSD]

I never gave you anything and don't care about your data. Surely you
can provide "DUMMY" data. Perhaps it is as simple as you are using a
formula for column L when you want another column. If so, you must
have attended Texas A & M.

That'a a rude thing to write! :-( I'm asking for help and I'm getting
wisecracks. I figured out the problem. My array L11:L131 contains the
results of a computation.The cells were originally initialized to 0. They
should have been initialized to "Null." If they're 0, then the last cell is
0, wherefor, my result cell is 0. When I initialized them to "Null" it
worked great.

BTW, I did print out my first post...and I did get the formula from you.
Take care.

Bernard

 

[BerrSD]

Why aren't you using?

=INDEX(B1:B4,MATCH(99999,B1:B4))

I figured out the problem. My array L11:L131 contains the results of a
computation.The cells were originally initialized to 0. They should have
been initialized to "Null." If they're 0, then the last cell is 0,
wherefor, my result cell is 0. When I initialized them to "Null" it worked
great.

Thanks for your help.

Bernard