If Statement with Vlookup

Hi brysonb12 ,

below there is the formula for Table3, but first you have to create 2 new columns, one in Table1 and the other in Table2. with the name of each table.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PLUjMqzRU0lFyLi0oBlKGSrE6KOJOiekgcSN08YDUPJC4MbK4EQ5zjHCYY4RiTiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Company = _t, Identifier = _t, Quantities = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Company", type text}, {"Identifier", type text}, {"Quantities", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Company", "Identifier"}, Company1, {"Company", "Identifier"}, "Changed Type", JoinKind.LeftOuter),
    #"Expanded Changed Type" = Table.ExpandTableColumn(#"Merged Queries", "Changed Type", {"Rates"}, {"Changed Type.Rates"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded Changed Type", {"Company", "Identifier"}, Company2, {"Company", "Identifier"}, "Company2", JoinKind.LeftOuter),
    #"Expanded Company2" = Table.ExpandTableColumn(#"Merged Queries1", "Company2", {"Rates"}, {"Company2.Rates"}),
    #"Inserted Maximum" = Table.AddColumn(#"Expanded Company2", "Rate", each List.Max({[Quantities], [Changed Type.Rates]}), type number),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Maximum",{"Changed Type.Rates", "Company2.Rates"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Result", each [Quantities]*[Rate]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Result", type number}})
in
    #"Changed Type1"