I'm currently working with financial data and I'm trying to use Index to lookup stock information on another sheet. Right now I have the below on my active sheet:
and on another sheet called "Data" I have the following:
What I'm trying to do is reference the information in "Data" in my active sheet with a formula by referencing both Column A and Row 1 (i.e. in the case of cell B2 I'd reference "Revenue" and "2020" to lookup that value in the Data sheet.
Here a the formula that works:
But I wonder if there's an easier way to do this. Is it possible to do this using a simpler function? I also keep getting errors that there's a circular reference in the calculation but I'm not sure why I'm getting that error.
Year | 2020 | 2019 | 2018 |
Revenue | |||
Revenue Growth | |||
Cost of Revenue | |||
Gross Profit | |||
Selling, General & Admin | |||
Research & Development |
and on another sheet called "Data" I have the following:
Year | 2020 | 2019 | 2018 |
Revenue | 32,184 | 32,136 | 32,765 |
Revenue Growth | 0.15% | -1.92% | 3.50% |
Cost of Revenue | 16,605 | 17,136 | 16,682 |
Gross Profit | 15,579 | 15,000 | 16,083 |
Selling, General & Admin | 6,929 | 7,029 | 7,602 |
Research & Development | 1,878 | 1,911 | 1,821 |
What I'm trying to do is reference the information in "Data" in my active sheet with a formula by referencing both Column A and Row 1 (i.e. in the case of cell B2 I'd reference "Revenue" and "2020" to lookup that value in the Data sheet.
Here a the formula that works:
Excel Formula:
=IFERROR(INDEX(Data!B:B,AGGREGATE(15,6,(ROW(Data!$A:$A)-ROW($B$1)+1)/(A:A=A2),ROWS($B$1))),"")
But I wonder if there's an easier way to do this. Is it possible to do this using a simpler function? I also keep getting errors that there's a circular reference in the calculation but I'm not sure why I'm getting that error.