Power Query and Power Pivot Table

Hi Exceler,

Hope this thread finds you well.


I am Trying to do the following step by using Power Query or by POwer Pivot table until now I did not find the correct way.

I have an excel file with two sheets.
First sheet contain 4 column A,B,C,D. The result should in new column F.
The second sheet contains three column A, B, C.

The column F will look first to column D If column D Exist in sheet 2 use it as result.
Other ways go and check Column A If lookup for A Exist in sheet 2 Column B take it as result
Other ways go and check in column B If lookup for B Exist in sheet 2 Column B take it as result
Other ways go and check in column C If vlookup for C Exist in sheet 2 Column B take it as result other way make result show as a blank cell


See the attachment, it contains a small example of what I want.


Hope you got what I mean.