CODEWITHSUNDEEP
excelexcel-formula
Michi
Michi

Reputation: 5471

Select row name based on variable column and search criteria

I have the following Excel spreadsheet:

     A                   B             C          D          E        F
1    KPI               Profit
2    Variancy            5%
3    Performance      very high
4
5    Description       Revenue       Costs       Profit
6    very low           -10%           -8%        -5%
7    low                 -5%           -3%        -2%
8    meaningless          0%            0%         0%  
9    high                 5%            3%         2%
10   very high           10%            8%         5%
11
12

The Cell B1 and B2 are input cells.

So far in Cell B3 I use the following formular to get the Description from the table based on the Input in Cell B2:

=INDEX($A$5:$D$10,MATCH(B2,$D$5:$D$10,1),1)

All this works fine.

However, now my issue is that I also want to include Cell B1 as variable into the formular so in case the user changes the input from Profit to Revenue the formular should automatically switch the column from $D$5:$D$10 to $B$5:$B$10.

What do I have to change in my formula to make this work?

Upvotes: 0

Views: 124

Answers (1)

EylM
EylM

Reputation: 6103

You can use IF formula and check what is the value B1 cell. Depending on the result, change the parameters you pass to MATCH formula:

=INDEX($A$5:$D$10,IF($B$1="Revenue",MATCH(B2,$B$5:$B$10,1),MATCH(B2,$D$5:$D$10,1)),1)

Upvotes: 1

Related Questions