Intersect row and column to return value

So you want to retrieve the value of a cell whose position is (X, Y)?

like this?:

you want the value from the grey-shaded cell?

if so, then you can do this by using the formula:

= offset(A1, <cell with X>, <cell with Y>, 1, 1)

B3=OFFSET(Table 1::A1, B2−1, B1−1)

this is shorthand for... select cell B3, then type (or copy and paste from here) the formula:

=OFFSET(Table 1::A1, B2−1, B1−1)

note the table names you use should match the demo tables in this post, or adjust the names to match your tables

I simply want to look down the first column of rows searching for X. Then I want to stop there, and look across the top row, looking for value Y. Where X and Y cross, I'l like to return that value.

In addition to OFFSET, you can use INDEX, which sometimes is less confusing because you don't have to subtract 1.

The formula in Table 2:

=INDEX(Table 1::A:D,2,3)

It goes down to row 2, then over to column 3, and returns the value.

=INDEX(<the range>, <row>, <column>)

SG

Formula in Table 2::C2 = OFFSET(Table 1::$A$1,MATCH(A2,Table 1::$A,0)−1,MATCH(B2,Table 1::$1:$1,0)−1)

Fill down

You may want to wrap it in an IFERROR function for those cases where it doesn't find X or Y in your table.

Ed,

I assume you would like this solution to find the proper row and column for you too. I think this will do what you want:

The expression for "Result" is:

=INDEX(Table 1::A:F, MATCH(A, Table 1::$A),MATCH(B, Table 1::$1:$1))

Regards,

Jerry

Edit: Sorry Badunit, I didn't see you had posted while I was answering. J.

Note Badunit has 0 as the third argument in MATCH, whereas Jerry does not. With 0 as the third argument, MATCH looks for the exact value. Without a third argument (or with a third argument of 1) MATCH looks for the largest value less than or equal to the first argument.

SG

Jerrold Green1 wrote:

Edit: Sorry Badunit, I didn't see you had posted while I was answering. J.

I do the same all the time.

I like the cleaner look of using INDEX. No need to subtract 1 from the MATCH results like I did using OFFSET. The upside to the OFFSET version is it will automatically include new rows and columns added to the bottom/right of Table 1 whereas the INDEX method will allow the addition of either new rows or new columns (depending on how you referenced Table 1) but not both.