Lookup data using an Offset to pull correct cells

[excel?]

I have data spread out over 17 columns and down 300 rows. I would like to retrieve the amounts from 2 cells relative to a cell that contains a specific number.

I'm hoping you can help with a formula that will lookup the specific number 84001 and retrieve the amount that is located 3 rows below it and a 2nd formula that looks up the same number and retrieves the amount located 3 rows below and 1 column to the right.

The number 84001 is located in a cell in the range C2:S330.

Does anyone have any suggestions on how to accomplish this?

Appreciate any help. Thanks.

 

[RasGhul]

Hi,

I have 84001 in cell U2 on my sheet, then you could search for any number within range C2:S330 then offset;

3rd row below

=OFFSET(INDIRECT(ADDRESS(SUMPRODUCT(($C$2:$S$330=$U$2)*(ROW($C$2:$C$330)-ROW($C$1)+1)),SUMPRODUCT(($C$2:$S$330=$U$2)*(COLUMN($C$2:$S$2)-COLUMN($C$2)+3)),4,1),TRUE),3,,)

3rd row below & 1 column to right

=OFFSET(INDIRECT(ADDRESS(SUMPRODUCT(($C$2:$S$330=$U$2)*(ROW($C$2:$C$330)-ROW($C$1)+1)),SUMPRODUCT(($C$2:$S$330=$U$2)*(COLUMN($C$2:$S$2)-COLUMN($C$2)+3)),4,1),TRUE),3,1,)

You can adjust the offset row or column by testing these numbers in the formula;

,TRUE),3,1,) for row & ,TRUE),3,1,) for column.