Sort of Vlookup in Powerpivot

[tomas]

I have a table with brands out of which the audited ones have different target audience and performance of every spot from audited brand is measured in all target audiences even though for each brand only one target audience is applicable.

Just simple example :

campaign spot male women

cola / 1 / 1,5 / 2,7
cola / 2 / 1,4 / 2,2
pepsi / 1 / 1,45 / 2,1

So lets say that pepsi campaign has target audience male and cola women. I want to create third column which would have only performance for target audience

so in previous example it would be

campaign spot male women Target

cola / 1 / 1,5 / 2,7 / 2,7
cola / 2 / 1,4 / 2,2 / 2,2
pepsi / 1 / 1,45 / 2,1 / 1,45

I managed to add calculated column with target audience with help of related function and then complicated thing happens. I have often many campaigns and many target audiences so if funcion seems evil and I would like if someone had idea how to do it without if .With If it look like this :

=IF([CalculatedColumn1]="A18+";[TRP_18];IF([CalculatedColumn1]="A25-60 BCD";[TRP_25_60BCD_2013];IF([CalculatedColumn1]="A25-45 BC";[TRP_25_45BC_2013];IF([CalculatedColumn1]="A30-45 BC";[TRP_30_45BC_2013];if([CalculatedColumn1]="A20-60 BCD";[TRP_20_60BCD_2013];IF([CalculatedColumn1]="M25-50";[TRP_M25_50];IF([CalculatedColumn1]="A25-60 ABCD";[TRP_25_60ABCD_2013];IF([CalculatedColumn1]="M30-60 ABC";[TRP_M30_60ABC_2013];"")

Furthermore this didnt work and annouce syntax error. Troubleshouting is hard because I can't use english version so in excel I use ; as argument separator. But if I want to this list of arguments in functions quite important if I nest, I need to use , because it doesn't move with ; . That at the end I swith all , for ;

Edit: I noticed missing few parenthesis

 

[ashish mehra]

Hi Tomas,

To start with you can refer to this link:

Consider posting a sample file.

The above explanation doesn't make any sense to me.

Regards,
AM

 

[tomas]

Hi Ashish

thanks for replying, I watch excelisfun quite often

Ok I post sample file already added table to data model. In this simplified example you see column Advertiser and then tree columns with results in different target groups. In Sheet one you see which advertiser has which target group. I want one calculated column in which I can see for coca cola results in its Target group, for Pepsi in its target group and so on.

I want to avoid nested ifs since there would be many in my particular job

 

[ashish mehra]

Hey Tomas,

I will not be able to check the attachment; chatting over phone.
Is expected result also there?

Regards,
AM

 

[tomas]

Now in column E I added expected result . Now I am sure its clear what I expect to accomplish