How to get the cell coordinates of a cell containing a search value?

Question

Level 1

8 points

How to get the cell coordinates of a cell containing a search value?

In Numbers (version 13.2, running under Sonoma 14.2.1), is is possible to search for a value (that might be contained in one of the cells of a table) and, if the value is found, the search function should return the coordinates of the cell that contains it.

For example:

In "Table1", cell K157 ("K" is the column, "157" is the row) contains the value "27/03/2021" (a date).

Is there a function that I can use to search for the string "27/03/2021" and which would return the value "K157" (the coordinates of the cell that contains the search-string)?

Thanks

Michael

MacBook Pro (2017 – 2020)

Posted on Jan 2, 2024 2:57 PM

Reply

Answer 1

Jan 2, 2024 5:01 PM in response to nazoreos

Michael,

Here's an example that might get you started. I used simple letters rather than dates, but you will get the idea.

Read the descriptions of MATCH and ADDRESS in the Formulas and Functions Help.

Jerry

Reply

Answer 2

nazoreos Author

Level 1

8 points

Jan 4, 2024 4:29 AM in response to Jerrold Green1

Hi Jerry,

thanks for your suggestions! I tried it out (it needed some mods, possibly due to different versions of Numbers?) and got it to do what you showed in your example. I also adapted it for my needs. So far so good.

The next stumbling block is to use the coordinates of the cell that I found in a summation function. What I would like to do is described in the example below:

Example:

I search for the string "12/01/2021" and I get the value "K27" in return; from this I extract the row number which is "27".

I then search for the string "03/07/2021" and I get the value "K235" in return; from this I extract the row number "235".

In some other cell a have a function which is the sum of the cells in the range "M27:M235". In other words, I need a summation function for a range of cells, where the range is defined as: <column_letter>(x):<column_letter>(y)

where:

<column_letter> can be the letter identifying any column in the table (not necessarily the column of the returned coordinates)
(x) and (y) are variables whose values I would provide from the returned coordinates

The "SUM" function does not appear to support this.

Thank you again for your help.

Michael

Reply

Answer 3

Jan 4, 2024 6:14 AM in response to nazoreos

Michael,

SUM is versatile. It will sum a comma-separated list, column(s), row(s), a rectangular area of cells, a list of any of the above, etc.

I don’t know what you have there now. If you could display your table and the important expressions, we can help.

Jerry

Reply