Formula that returns a value from a different row and column in data set

[Alteryx102]

Hello,

I am looking to for a way or formula to return a value from a different row and column if possible. I have attached a example dataset.

In this data set, I am looking at row 2 ie. Division 1 to return values in. For example, in this row for the column called "3221 Rev", I want it to return the value in row 3 ie. 3221 Division and column "2021 Revenue" which would be 6,000,000. And so on for each column.

Essentially in row 1, the blank columns should return the corresponding 2021 Rev for that Division. 

Any help would be greatly appreciated.

Thank you!

[TheOC]

hi @Alteryx102 

Please find attached method to do this:

Thanks,
TheOC

[Qiu · Accepted solution]

@Alteryx102 
It will better if you can provide a sample output.
but I imagine maybe it is something like this?

[TheOC · Accepted solution]

Whoops - forgot to attach, please find attached to this message!

Cheers,
TheOC

[atcodedog05]

Hi @Alteryx102 

Here is a dynamic solution it is independent of the column names and will adapt as the rev column names changes and will pick only those present in the columns.

Hope this helps : )

[Alteryx102]

Thank you everyone, these work great and have solved my inquiry! 

[Qiu]

@Alteryx102 
Good to know it helps and thank you for the accept mark 😁