Offset function - rows AND columns

[eyalgrn]

Hello,

I have found the way to refer to a cell from either row(s) OR column(s). However, I am looking for a function/solution to refer to a cell from another cell which its location is given by BOTH rows and columns, compared to the cell. For example, each cell in a certain column of my dataset will get the value of the cell that located two columns to the left AND one row to the top. 

Basically it's the offset function in excel. I have attached an example in excel.

Thanks!

Eyal

[Federica_FF · Accepted solution]

Hi @eyalgrn,

the Multi Row Formula Tool could be the tool you need.

ID = [Row-3: SKU]

Maybe including an IF statement that avoids to overwrite the first 3 values (A,B,C).

Let's say the first 3 records are A, B, C and then you only have null values

ID=

IF ISNULL(ID) then [Row-3: SKU]

else [SKU]

endif

Or together with a RecordID

IF [RecordID]>3 then [Row-3: SKU]

else [SKU]

endif

 Something in this direction.

[eyalgrn]

Perfectly works!

Thanks!!

[Inactive User]

I have the same issue but with millions of rows and columns. Is there any other way?

I have to move rows and columns both step by step.

Please guide.