Ask Your Question

How to search and get back a cell reference?

asked 2021-04-15 23:17:27 +0200

abrogard gravatar image

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?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2021-04-16 06:51:39 +0200

JohnSUN gravatar image

updated 2021-04-16 07:31:24 +0200

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")


edit flag offensive delete link more


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. :)

abrogard gravatar imageabrogard ( 2021-04-16 06:57:37 +0200 )edit

Perhaps you could explain what "doing something" involves; it is possible someone can suggest an alternate way of solving your problem.

robleyd gravatar imagerobleyd ( 2021-04-16 07:27:08 +0200 )edit

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.

abrogard gravatar imageabrogard ( 2021-04-16 08:37:47 +0200 )edit

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?

JohnSUN gravatar imageJohnSUN ( 2021-04-16 09:44:53 +0200 )edit

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 ...(more)

abrogard gravatar imageabrogard ( 2021-04-16 11:14:41 +0200 )edit

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.

JohnSUN gravatar imageJohnSUN ( 2021-04-16 11:23:15 +0200 )edit

yup, I'll wait. that'd be nice. thanks a lot. meanwhile I'll see what i can do, stumbling and fumbling around... :)

abrogard gravatar imageabrogard ( 2021-04-16 11:38:19 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2021-04-15 23:17:27 +0200

Seen: 39 times

Last updated: Apr 16