Finding a cell and returning value from a different row and column

[RJBohn3]

I have a term in cell F2. I'm trying to use that term to return a value from
a cell in a different row and column.

I have the INDEX/MATCH formula that will find the same term from column A
(A922). So I can get the cell(F2) to find the contents of the INDEX/MATCH.

Where I am stumped is trying to get the formula to lookup the term and then
return a certain value from a cell that is 4 rows down and 15 columns right
(Cell O926).

I know I can make a formula to find the exact contents of that cell. That's
not what I need. What I need is a generic formula so that I can change the
contents of cell F2, the formula will find the contents in column A, and then
lookup the value 4 rows down and 15 columns right. It has a certain piece of
information I need.

 

[smartin]

I have a term in cell F2. I'm trying to use that term to return a value from
a cell in a different row and column.

I have the INDEX/MATCH formula that will find the same term from column A
(A922). So I can get the cell(F2) to find the contents of the INDEX/MATCH.

Where I am stumped is trying to get the formula to lookup the term and then
return a certain value from a cell that is 4 rows down and 15 columns right
(Cell O926).

I know I can make a formula to find the exact contents of that cell. That's
not what I need. What I need is a generic formula so that I can change the
contents of cell F2, the formula will find the contents in column A, and then
lookup the value 4 rows down and 15 columns right. It has a certain piece of
information I need.

So, INDEX has three pieces:

- the array to return from
- the row to return from within the array
- the column to return from within the array

I presume you are using MATCH to get the latter two pieces.

If you make the array to return from sufficiently larger so it includes
all possible return values, you can add to the row and column MATCHes
with displacement values 4 and 15:

=INDEX(LargerArray,MATCH(...)+4,MATCH(...)+15)

Or, you can use OFFSET and not worry about the size of the return array,
but you have to be careful about how the displacement values are
interpreted:

=OFFSET(TopLeftCornerOfArray,MATCH(...)+3,MATCH(...)+14)