LOOKUPVALUE WITH IF

[twins2019]

HI

i have the following table2

cust	COUNTRY	Market Responsibility	Customer Market Area New
	AE	Asia Minor	EMEA
	AL	South East EU	EMEA
	AM	Russia	EMEA
	AN	USA	North America
	AR	Other South America	South America
	AT	Central Europe	EMEA
at99999	AT	Italy	EMEA
at56432	AT	Asia Minor	EMEA
at72444	AT	Asia Minor	EMEA

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 

[Anonymous]

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"

[twins2019]

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

[Nathaniel_C]

Hi @twins2019 ,

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