Excel: Find intersection of a row and a column

问题:

My question is how can I find an intersecting cell of a specific column and row number?

My situation is this: with some calculations I find two cells, lets say B6 and E1. I know that I need a row of the first one and a column of the second one. So I could just use ROW and COLUMN functions to get the numbers. After that, I need to find an intersecting cell. Which would be E6 in this example.

I would just use INDEX(A1:Z100;ROW;COLUMN) but I don't know the exact area that I'm going to need - it depends on other stuff. I could use something like A1:XFG65000 but that is way too lame. I could also use a combination of INDIRECT(ADDRESS()) but I'm pulling data from a closed workbook so INDIRECT will not work.

If this would help to know what is this all for - here's a concrete example: I need to find limits of a section of a sheet that I would work with. I know that it starts from the column B and goes all the way down to the last non-empty cell in this column. This range ends with a last column that has any value in first row. So to define it - I need to find the intersection of this last column and the last row with values in B column. I use this array formula to find the last column:

INDEX(1:1;MAX((1:1<>"")*(COLUMN(1:1))))

And this array formula to find the last row:

INDEX(B:B;MAX((B:B<>"")*(ROW(B:B)))

Last column results in E1 and last row results in B6. Now I need to define my range as B1:E6, how can I get E6 out of this all to put into the resulting formula? I've been thinking for a while now and not being and Excel expert - I couldn't come up with anything. So any help would really be appreciated. Thanks!

回答1:

You can use an Index/Match combination and use the Match to find the relevant cell. Use one Match() for the row and one Match() for the column.

The index/match function to find the last cell in a sheet where

• column B is the leftmost table column
• row 1 is the topmost table row
• data in column B and in row 1 can be a mix of text and numbers
• there can be empty cells in column B and row 1
• the last populated cell in column B marks the last row of the table
• the last populated cell in row 1 marks the last column of the table

With these premises, the following will return correct results, used in a Sum() with A1 as the starting cell and Index to return the lower right cell of the range:

=SUM(A1:INDEX(1:1048576,MAX(IFERROR(MATCH(99^99,B:B,1),0),IFERROR(MATCH("zzzz",B:B,1),0)),MAX(IFERROR(MATCH(99^99,1:1,1),0),IFERROR(MATCH("zzzz",1:1,1),0))))

Since you seem to be on a system with the semicolon as the list delimiter, here is the formula with semicolons:

=SUM(A1:INDEX(1:1048576;MAX(IFERROR(MATCH(99^99;B:B;1);0);IFERROR(MATCH("zzzz";B:B;1);0));MAX(IFERROR(MATCH(99^99;1:1;1);0);IFERROR(MATCH("zzzz";1:1;1);0))))

回答2:

Offset would seem to be the way to go

=OFFSET($A$1,ROW(CELL1)-1,COLUMN(CELL2)-1)

(The -1 is needed because we already have 1 column and 1 row in A1)

in your example, =OFFSET($A$1,ROW(B6)-1,COLUMN(E1)-1) would give the value in E6

There is also ADDRESSS if you want the location: =ADDRESS(ROW(B6),COLUMN(E1)) gives the answer $E$6