Locating random cells by their value

[Guest]

What I actually want is to use the value of a cell which is next to another whose value is zero and which is going to be the only zero in that column.

x y a c b d q
520 182 1 0 0 1 2
182 156 0 1 1 -2 1
156 26 1 -1 -2 3 6
26 0 -1 7 3 -20 #DIV/0!
0 #DIV/0! 7 #DIV/0! -20 #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!

I want to try and indentify that 26 from the fact that next to it you have a 0. The thing is, depending on the numbers under x and y this zero will appear at a different row value and so the number I want can appear pretty much anywhere.

Hope someone can help me as it's starting to get a bit frustrating.

 

[Leo Heuser]

Dan

Is this, what you're looking for?

=INDEX(A2:A100,MATCH(0,B2:B100,0))

assuming x is column A and y is column B

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

What I actually want is to use the value of a cell which is next to

another whose value is zero and which is going to be the only zero in that
column.

x y a c b d q
520 182 1 0 0 1 2
182 156 0 1 1 -2 1
156 26 1 -1 -2 3 6
26 0 -1 7 3 -20 #DIV/0!
0 #DIV/0! 7 #DIV/0! -20 #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!

I want to try and indentify that 26 from the fact that next to it you have

a 0. The thing is, depending on the numbers under x and y this zero will
appear at a different row value and so the number I want can appear pretty
much anywhere.

 

[Guest]

FANTASTIC! Thanks very much. Problem solved.

Dan

Is this, what you're looking for?

=INDEX(A2:A100,MATCH(0,B2:B100,0))

assuming x is column A and y is column B

--
Best Regards
Leo Heuser

Followup to newsgroup only please.


another whose value is zero and which is going to be the only zero in that
column.
a 0. The thing is, depending on the numbers under x and y this zero will
appear at a different row value and so the number I want can appear pretty
much anywhere.

 

[Leo Heuser]

You're welcome and thanks for the feedback.

LeoH