Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

LOOKUPVALUE WITH IF

HI

i have the following table2

custCOUNTRYMarket ResponsibilityCustomer Market Area New
 AEAsia MinorEMEA
 ALSouth East EUEMEA
 AMRussiaEMEA
 ANUSANorth America
 AROther South AmericaSouth America
 ATCentral EuropeEMEA
at99999ATItalyEMEA
at56432ATAsia MinorEMEA
at72444ATAsia MinorEMEA

 

i need a formula in table1 that find the column "market responsability" in table2 with these criteria :

if the customer number in table 1 is present in column "cust" in table2 then look for the market responsability in table2 

if the customer is not present, then it crosses me the column iso in table1 with column country in table2 and return the "market rasponsability" ... 

 

sorry for my bad english :-(

thanks to all

paola 

Tips and Tricks

3 Replies

  • Nathaniel_C's avatar
    Nathaniel_C
    Community Champion
    6 years ago

    Hi Anonymous ,

    Not quite sure what exactly the issue is here.  You may want to type it up in your language and use Google to translate.  I find it works quite well.

    Thank you.

    Nathaniel

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Paolo,

     

    I can understand your question just fine. You will need to perform two merges that will perform just like you described. Lookup Table1 into Table2 by customer number to cust and call this Lookup1. Now lookup Table1 into Table2 by iso to country. Expand Lookup1 into Market Responsiblity and Lookup2 into Customer Market New Area. Next put in a calculation field that will pull the Market Responsiblity if it is not null and will otherwise return the Customer Market New Area. Finally, remove the Market Responsiblity and Customer Market New Area.

     

    Regards,

    Mike

     

    let
    Source = Table1,
    #"Merged Queries" = Table.NestedJoin(Source, {"customer number"}, Table2, {"cust"}, "Lookup1", JoinKind.LeftOuter),
    #"Merged Queries1" = Table.NestedJoin(#"Merged Queries", {"iso"}, Table2, {"COUNTRY"}, "Lookup2", JoinKind.LeftOuter),
    #"Expanded Lookup1" = Table.ExpandTableColumn(#"Merged Queries1", "Lookup1", {"Market Responsibility"}, {"Market Responsibility"}),
    #"Expanded Lookup2" = Table.ExpandTableColumn(#"Expanded Lookup1", "Lookup2", {"Customer Market Area New"}, {"Customer Market Area New"}),
    AddLookup = Table.AddColumn(#"Expanded Lookup2", "Lookup", each if [Market Responsibility] <> null then [Market Responsibility] else [Customer Market Area New]),
    #"Removed Columns" = Table.RemoveColumns(AddLookup,{"Market Responsibility", "Customer Market Area New"})
in
    #"Removed Columns"

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago
      I have to have in the table 1 the market responsabilty that is in table 2 crossing where is present the customer code of the table 2 and where the customer code is not present, the country code

      I m working with power pivot and i have tried with IF and looukvalue but it return always ERROR