So say I have data like this:

I want to find the date data highlighted in yellow using the value "Production" highlighted in green in the A column. However, the cell where location resides may change (the column does not change) as the table is updated from outside sources. How could I use the OFFSET function to get that date data? Or is there a better function to use?

CodePudding user response：

The formula below assumes that your data don't extend outside the A1:AZ1000 range:

=INDEX(A1:AZ1000,MATCH("production",A1:A1000,0) 1,AGGREGATE(15,6,POWER(10,LOG10((B2:AZ2>0)*COLUMN(B2:AZ2))),1))

(the references to row 2 are because the data in your screenshot start on that row, so it identifies the first numeric column as the first column in B2:AZ2 containing a value > 0)

The assumption made is that you want the value from the 1st numeric column that is on the row below where production is found (as, if you simply wanted the last row of the first column of numeric data then the position of production would be irrelevant).

(if your version of Excel is earlier than 2021/Office 365 then the formula must be entered as an array formula)

CodePudding user response：

=OFFSET(A1,MATCH("production",$A:$A,0),4,1,1)

Match finds the row of "production" Offset uses the match as it's 2nd argument (rows) from A1, then over 4 columns and retrieve 1x1 cells.