I can’t find a function that will enable me to search a range for a number and get back a cell reference for where it is found.
Is there such a function?
The answer to your question is NO - there is no function that will do exactly what you described.
And the answer to your question is YES - the MATCH() function will find the position of the desired cell, the ADDRESS() function will form the cell address, the IFERROR() function will handle the situation when the desired cell does not exist
By the way, the cell address is rarely needed - for example, to form a dynamic hyperlink to quickly jump to the desired place in the table. Usually, the relative position of the cell is used instead address to get the value in the same row several columns to the right (VLOOKUP()) or to the left-higher-lower (INDEX()) or just somewhere nearby (OFFSET())
Update Example
=IFERROR(ADDRESS(MATCH(E2;C1:C9;0);3;4);"No match")
Well that’s very good news.
I need this function I think. I’m putting together a convoluted way of doing something because I can’t find another way.
I’m not even quite sure how it’s going to go yet but first this info if I can get it.
Can you help me with an example formula line then? Because I thought match() and address() might be the way but I couldn’t get it to work.
Perhaps you could explain what “doing something” involves; it is possible someone can suggest an alternate way of solving your problem.
not so far they can’t. it’s in another thread. either way it’d still be nice to know this wouldn’t it?
p.s. but if you would like to have a go a little sheet demonstrating the problem is here: (I can’t find words to succinctly say it. It’s something about summing a portion of a column when you don’t know how big the portion is or where it is. http://abrogard.com/tinytwo.ods
It seems to me that you complicated your task when you did not formulate it accurately. Let’s say this: “Subtract 250 from all receipts once a week.” In this case, checking the cell with the week number using the usual IF () will allow subtracting from the “start” column either 250 if there is a week number, or 0 if the week number cell is empty. Or did you mean something else?
That sounds about right to me.
But I’m struggling to get my head around your scheme. I don’t quite understand.
You mean log the receipts as they come in with a row for each, just as I do. Unpredictable number of receipt days.
Then on a week day total them.
So I’ve got a column for the receipts.
And a column for the ‘week days’
and a column that says if it is a ‘week day’ then total the receipts. From when? From after the last week day.
How do we know which row that was? That’s what I want to get cell references for I think.
So’s I can know where to start my summing from.
I’ll bet I haven’t understood…
Thanks for the formula. It’s working fine. Threw me off a bit at first when it returned Cx when it should have been Ax.
Then I found that parameter. Now it does the job.
I’ll persevere and see if I can get to whatever way it is that i’m trying to put together.
But if you can illustrate your way I’d sure like to see it.
Ok, wait a little. Now I will return to the topic where @Lupp recommended changing the design of the sheet and try to tell how I would solve your problem.
yup, I’ll wait. that’d be nice. thanks a lot. meanwhile I’ll see what i can do, stumbling and fumbling around…