Home » Two-Way approximate Lookup with multiple criteria

Two-Way approximate Lookup with multiple criteria

Lookup the Approximate Milk Purity Dynamic array function allowed but Extra marks for Legacy Array Functions or PowerQuery Solution

📌 Challenge Details and Links
Challenge Number: 18
Challenge Difficulty: ⭐⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Two-Way approximate Lookup with multiple criteria with Power Query

Power Query solution 1 for Two-Way approximate Lookup with multiple criteria, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Rows = Table.SelectRows(Source, each [Milk] = "Premium Milk"), 
  Sort = Table.SelectColumns(
    Rows, 
    List.FirstN(Table.ColumnNames(Source), 2)
      & List.Sort(List.Skip(Table.ColumnNames(Source), 2), each Number.From(_))
  ), 
  Time = List.PositionOf(Sort[Time], List.Select(Sort[Time], each _ < 184){0}), 
  Temp = List.PositionOf(
    Table.ColumnNames(Sort), 
    Text.From(
      List.Last(
        List.Select(
          List.Transform(List.Skip(Table.ColumnNames(Sort), 2), Number.From), 
          each _ < 147
        )
      )
    )
  ), 
  Sol = Number.ToText(Table.ToRows(Sort){Time}{Temp}, "P0")
in
  Sol
Power Query solution 2 for Two-Way approximate Lookup with multiple criteria, proposed by Brian Julius:
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Lookup = {"Premium Milk", "184", "147"}, 
  UnpivotOther = Table.TransformColumnTypes(
    Table.UnpivotOtherColumns(Source, {"Milk", "Time"}, "Temperature", "Purity"), 
    {"Temperature", Decimal.Type}
  ), 
  Group = Table.Group(
    UnpivotOther, 
    {"Milk", "Time"}, 
    {
      {
        "All", 
        each _, 
        type table [Milk = text, Time = number, Temperature = number, Purity = number]
      }
    }
  ), 
  AddTempRank = Table.RemoveColumns(
    Table.AddColumn(
      Group, 
      "TempRank", 
      each Table.AddRankColumn(
        [All], 
        "TempRnk", 
        {"Temperature", Order.Ascending}, 
        [RankKind = RankKind.Dense]
      )
    ), 
    "All"
  ), 
  Expand = Table.ExpandTableColumn(
    AddTempRank, 
    "TempRank", 
    {"Temperature", "Purity", "TempRnk"}, 
    {"Temperature", "Purity", "TempRnk"}
  ), 
  Filter = List.Last(
    Table.ToColumns(
      Table.SelectRows(
        Expand, 
        each ([Milk] = Lookup{0})
          and ([Time] <= Number.From(Lookup{1}))
          and ([Temperature] <= Number.From(Lookup{2}))
      )
    ){3}
  )
in
  Filter
Power Query solution 3 for Two-Way approximate Lookup with multiple criteria, proposed by Bhavya Gupta:
let
  lookup = [Milk = "Premium Milk", Time = 184, Temp = 147], 
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "dZE7DsIwEESvElmiSxGHrL2+ABVISBQUUQqKABGfSEHch7NwMuJZHAeRNNN43+zsuCzVrj10yaa5XlSq8px6NbTo1eVeKfNq2SvbharSUu3P7bVOVl39OA+g9SBpjC2BAHRQYwTcdvWted4GKMsGyFqowTg2m6VA6/Z+StbNsQb2fvVPGtsYoZzgo82GBfw5TOMwG+1ZFB62mD8MjXxziT3FXrzVxGGaslhdAXWxk9DGxDYt/QuSh+FQY4g51cjo42gUUIJT8d+IdOgwy5gtkJFN+PC5jEzxvyhWKQu5N6mqDw==", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [
        Milk = _t, 
        Time = _t, 
        #"37.5" = _t, 
        #"150.7" = _t, 
        #"70.3" = _t, 
        #"140.6" = _t, 
        #"96.6" = _t
      ]
  ), 
  UnPivot = Table.UnpivotOtherColumns(Source, {"Milk", "Time"}, "Temp", "Purity"), 
  CT = Table.TransformColumnTypes(
    UnPivot, 
    {{"Temp", type number}, {"Purity", Percentage.Type}, {"Time", Int64.Type}}
  ), 
  Grouped = Table.Group(CT, {"Milk"}, {{"All", each _}}){[Milk = lookup[Milk]]}[All], 
  Output = Table.Sort(
    Table.SelectRows(Grouped, each ([Time] <= lookup[Time]) and ([Temp] < lookup[Temp])), 
    {{"Time", 1}, {"Temp", 1}}
  ){0}[[Purity]]
in
  Output

Solving the challenge of Two-Way approximate Lookup with multiple criteria with Excel

Excel solution 1 for Two-Way approximate Lookup with multiple criteria, proposed by Kris Jaganah:
=LET(
   a,
   FILTER(
       HSTACK(
           Time,
           Purity),
       Milk=K4),
   XLOOKUP(
       K5,
       TAKE(
           a,
           ,
           1),
       XLOOKUP(
           K6,
           Temp,
           DROP(
           a,
           ,
           1),
           ,
           -1),
       ,
       -1))
Excel solution 2 for Two-Way approximate Lookup with multiple criteria, proposed by Julian Poeltl:
=INDEX(
   Purity,
   XMATCH(
       K4&K5,
       Milk&Time,
       -1),
   XMATCH(
       K6,
       Temp,
       -1))

I'm not so sure about the approximation - the nearest value would be more accurate than the same or smaller value.
But with the match mode nearest value my result would be 0.768197932482401 - so insted of 150s and 140.6°C 200s and 150.7°C (both for premium milk)
Excel solution 3 for Two-Way approximate Lookup with multiple criteria, proposed by Milan Shrimali:
=INDEX(
   D5:H15,
   XMATCH(
       K5&K6,
       B5:B15&C5:C15,
       -1),
   XMATCH(
       K7,
       D4:H4,
       -1))

Leave a Reply