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

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!

data example.xlsx

Datasets

Tips and Tricks

Preparation

Accepted answers

Qiu

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

0227-Alteryx102.yxmd

TheOC

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

Cheers,
TheOC

Alteryx102.yxzp

All comments

TheOC

hi @Alteryx102

Please find attached method to do this:

Thanks,
TheOC

Qiu

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

0227-Alteryx102.yxmd

TheOC

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

Cheers,
TheOC

Alteryx102.yxzp

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 : )

Rev.yxmd

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 😁

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703