Excel: Find intersection of a row and a column

Question

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!

Answer 1

The following webpage has a much easier solution, and it seems to work.

https://trumpexcel.com/intersect-operator-in-excel/

For example, in a cell, type simply: =C:C 6:6. Be sure to include one space between the column designation and the row designation. The result in your cell will be the value of cell C6. Of course, you can use more limited ranges, such as =C2:C13 B5:D5 (as shown on the webpage).

As I was searching for the answer to the same basic question, it astounded me that there is no INTERSECT worksheet function in Excel. There is an INTERSECT feature in VBA (I think), but not a worksheet function.

Anyway, the simple spacing method shown above seems to work, at least in straightforward cases.

Answer 2

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))))

Answer 3

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