In numbers, SUMPRODUCT (range, range…) takes the following arguments:
range: A range of cells. range is a reference to a single range of
cells containing values of any type. If string or Boolean values are
included in range, they are ignored.
range…: Optionally include one
or more additional ranges of cells. The ranges must all have the same
dimensions.
So you'll need to set up a range to perform the initial calculation (and squaring) and then sumproduct two simple ranges.
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 column B. Here's the Applescript I used via Automator:
on run {input, parameters}
tell application "Numbers"
tell table "Calculator 1" of sheet 2 of document of front window
set aRange to "A2:A22"
set bRange to "B2:B22"
set aRows to the rows of range aRange
set bRows to the rows of range bRange
set correctRow to -1
set correctCol to -1
repeat with i from 1 to count of bRows
set aCell to "A" & (i + 1)
set bCell to "B" & (i + 1)
if value of (cell bCell) is not "not" then
set correctRow to value of (cell aCell)
set correctCol to value of (cell bCell)
end if
end repeat
set the value of (cell "C2") to correctRow
set the value of (cell "C4") to correctCol
end tell
display dialog "Updated!"
end tell
return input
end run
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.
Create a table has the name of the each row in each row of the header column, and use the desired MATCH() function per row in this way (see the picture of the table in my question).
Use this Applescript to loop through the MATCH() functions and find the correct row and column. Change "C2" to whichever cell will be your Correct Row and "C4" to whichever cell will be your Correct Column.
Use the INDEX() function to get the value of the cell and put it into another cell, using the coordinates the Applescript calculated for you.
Best Answer
You can do this with another area used for intermediate computation. I use another table on a separate Reference sheet.
Example:
(The bottom row would be in the other table.)