Hi,
I was wondering if anyone could help me. I have a spreadsheet of month columns against category rows. I want to select the month and category from "pop-up menu" cells, which I can do OK. But then I want to display the data at the intersection cell in a separate spreadsheet. See Below.
Hopefully this makes sense. Thanks for taking the time to read my request.
iMac 27″, macOS 11.7
=INDEX(Table 1::$A:$G,MATCH(B2,Table 1::$A,0),MATCH(B1,Table 1::$1:$1,0))
or
=INDEX(Table 1::$1:$7,MATCH(B2,Table 1::$A,0),MATCH(B1,Table 1::$1:$1,0))
Which one you use depends on which way your table might expand in the future. You probably want the first one.
The first formula uses the range Table 1::A:G to reference the entire table. I assume you will be adding more columns for the rest of the months so the "G" will actually be "M" for your complete table. With Table 1::A:M you can add more rows to Table 1 and you can add new columns between A and M without having to edit the formula. They will automatically be included. If you add new columns to the left or right, the formula will have to be edited to include them.
The second uses Table1::1:7 to reference the entire table. With this one you can add new columns and they will automatically be included in the formula and you can add new rows between 1 and 7 but you cannot add new rows above 1 or below 7 without editing the formula to include them.
=INDEX(Table 1::$A:$G,MATCH(B2,Table 1::$A,0),MATCH(B1,Table 1::$1:$1,0))
or
=INDEX(Table 1::$1:$7,MATCH(B2,Table 1::$A,0),MATCH(B1,Table 1::$1:$1,0))
Which one you use depends on which way your table might expand in the future. You probably want the first one.
The first formula uses the range Table 1::A:G to reference the entire table. I assume you will be adding more columns for the rest of the months so the "G" will actually be "M" for your complete table. With Table 1::A:M you can add more rows to Table 1 and you can add new columns between A and M without having to edit the formula. They will automatically be included. If you add new columns to the left or right, the formula will have to be edited to include them.
The second uses Table1::1:7 to reference the entire table. With this one you can add new columns and they will automatically be included in the formula and you can add new rows between 1 and 7 but you cannot add new rows above 1 or below 7 without editing the formula to include them.
Or use the OFFSET function to count down and over from the base cell A1 in Table 1.
=OFFSET(Table 1::$A$1,XMATCH(B2,Table 1::A),XMATCH(B1,Table 1::$1:$1))
The first XMATCH determines how many rows down from A1 by looking for a match in Column A.
The second XMATCH determines how many columns over from A1 by looking for a match in Row 1.
You can expand Table 1 horizontally and vertically without having to change the formula.
More on these functions, with examples, can be found via Help > Formulas and Functions Help in the menu or by following these links:
SG
That is a slick feature of Numbers. If there are more than one of these tables (for example, a copy of this sheet for each year), the sheet and table will have to be part of the reference to remove the ambiguity.
=INDIRECT("Sheet 1::Table 1::"&B1&" "&B2)
You could use the INDIRECT function like this:
Cell B1 in Table 2 contains the Pop-up menu: January, … , June.
Cell B2 in Table 2 contains the Pop-up menu: Food, … , Phone.
Replace , by ; depending on your region.
Paul.
Thanks, This has worked fine.
Thanks, this is the solution I have used.
Intersection of column and row in numbers
