I have a table in Numbers, and I'm trying to get the value of the first cell that contains the character …
. In order to do so, I wanted to use MATCH()
, but had no luck after I realized it could only traverse 2 dimensions. Instead, I've set up a table on another sheet that looks like so:
The numbers at the far left are the row numbers of the rows in the table with the actual data. Row Contains Cell
is exactly what it sounds like: whether or not that row contains the desired cell. To figure that out, I used MATCH()
per row.
What I need to figure out now is how to find which row is not the wrong row. It would be easy to look for a cell that says not
, but I need to find a cell with a variable value that is not equal to not
. I want to find the row name (or value of the cell to the left, in column A
) for that cell (in this case, 2
) and the value of the cell itself in column B
.
What would be the formula for Correct Row
and Correct Column
, given what I'm looking for?
I know people have already done this for Excel, but I can't figure it out for Numbers.
I'm open to solutions that require Applescript.
Best Answer
As it turns out, I've figured it out. It did require Applescript. What I did was loop through the rows and selected the values in the row where
"not"
was not present in columnB
. Here's the Applescript I used via Automator:Keep in mind that the cells I used are specific to my spreadsheet, but generally this method could be applied for any 2D
MATCH()
function.MATCH()
function per row in this way (see the picture of the table in my question).MATCH()
functions and find the correct row and column. Change"C2"
to whichever cell will be yourCorrect Row
and"C4"
to whichever cell will be yourCorrect Column
.INDEX()
function to get the value of the cell and put it into another cell, using the coordinates the Applescript calculated for you.