Hello,
I would like to pull the address of a cell based on its content. Currently I have values in fields C1:C3 that are 50,0,0. I would like a formula which can pull the location of "C1" based on the fact that it is the only cell in the array which does not equal 0. I have had no luck creating one myself. Any help would be appreciated!
Thanks!
..........................................................
Last edited by Pepe Le Mokko; 06-10-2015 at 12:45 PM.
Here's one way, though I agree with Pepe
="C"&MATCH(SUM(C1:C3),C1:C3,0)
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
Could there be more than one cell that does not contain zero? If so, what result do you expect?
Will there be any negative numbers?
Do you really want the cell address or do you want the actual value of the cell?
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
I do need it for another formula. Can you elaborate on how you would used the index formula? I attempted to but had no luck.
Thanks!
....................................................................
Last edited by Pepe Le Mokko; 06-10-2015 at 12:46 PM.
Yes, it is possible that there is more than one cell that does not contain zero. If that were the case then I would just want it to reference the cell furthest down. For example if, A1=10, A2=20, A3=0 -- I would like the formula to pull A2. And no, there will not be any negative numbers.
Thanks!
I'm guessing that you don't need the cell address. You should be able to use the cell value in your other formula.
This will return the bottom-most value in the range that is >0:
=LOOKUP(2,1/(A1:A3>0),A1:A3)
I believe that I do because I will be offsetting the result of this search by -1, so if the formula returns C1 the end formula will be using B1
How about telling us what your end goal is?
Maybe this...
=LOOKUP(2,1/(C1:C3>0),B1:B3)
No address necessary!
Last edited by Tony Valko; 06-10-2015 at 12:46 PM.
Tony, that worked for what I am attempting to do. Thanks so much! Would you mind explaining the parts of the formula to me? I cannot follow the logic for why it works.
Here is a link to an explanation I wrote that is very similar to your application.
Basically, the only difference is the range from which the result is taken.
https://www.excelforum.com/showthread.php?p=3398950
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks