Excel - Find Column and Row from Table-CodePudding

I'm trying to build a Sudoku calculator using only Excel (no VBA). As part of the process, I'm using the Position table (see image) as my reference for each cell. If I look at say position 12 in the table, how would I pull column 4 in one formula and row 2 in another formula?

CodePudding user response：

Use AGGREGATE:

Row:

=AGGREGAT(15,7,(ROW($N$5:$V$13)-MIN(ROW($N$5:$V$13)) 1)/($N$5:$V$13=12),1)

COLUMN:

=AGGREGAT(15,7,(COLUMN($N$5:$V$13)-MIN(COLUMN($N$5:$V$13)) 1)/($N$5:$V$13=12),1)

replace the =12 with = and your cell reference.

But if you use base 9 with two digits, you can just parse the number:

ie:

Now the first digit 1 is the row and the second 1 is the column