Lookup correct grades per subject Marks below lowest grade is a fail Dynamic array function allowed, but Extra marks for Legacy solutions or PowerQuery Solution
📌 Challenge Details and Links
Challenge Number: 21
Challenge Difficulty: ⭐
📥Link to the solutions on LinkedIn
Solving the challenge of Approximate Lookup with Power Query
Power Query solution 1 for Approximate Lookup, proposed by Omid Motamedisedeh:
let
S1 = Excel.CurrentWorkbook(){[Name = "tblGrades"]}[Content],
S2 = Excel.CurrentWorkbook(){[Name = "tblStudents"]}[Content],
C1 = Table.AddColumn(
S2,
"Grades",
each try
Table.SelectRows(S1, (x) => x[Subject] = _[Subject] and x[Marks From] <= _[Marks])[Grade]{0}
otherwise
"Fail"
)
in
C1Power Query solution 2 for Approximate Lookup, proposed by Aditya Kumar Darak 🇮🇳:
let
Grades = Excel.CurrentWorkbook(){[Name = "tblGrades"]}[Content],
Students = Excel.CurrentWorkbook(){[Name = "tblStudents"]}[Content],
Return = Table.AddColumn(
Students,
"Grade",
each [
S = Table.SelectRows(Grades, (f) => f[Subject] = [Subject] and f[Marks From] <= [Marks]),
R = S[Grade]{0}? ?? "Fail"
][R]
)
in
ReturnPower Query solution 3 for Approximate Lookup, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "tblGrades"]}[Content],
Subjetcs = Table.Group(Source, {"Subject"}, {{"All", each _}}),
Grades = Excel.CurrentWorkbook(){[Name = "Students"]}[Content],
Sol = Table.AddColumn(
Grades,
"Grade",
(x) =>
let
a = Table.SelectRows(Subjetcs, each x[Subject] = [Subject])[All],
b = Table.SelectRows(a{0}, each x[Marks] > [Marks From])[Grade]{0}? ?? "Fail"
in
b
)
in
SolPower Query solution 4 for Approximate Lookup, proposed by Luan Rodrigues:
let
Fonte = tblStudents,
res = Table.AddColumn(
Fonte,
"Personalizar",
each Table.SelectRows(tblGrades, (x) => [Subject] = x[Subject] and x[Marks From] <= [Marks])[
Grade
]{0}?
?? "Fail"
)
in
resPower Query solution 5 for Approximate Lookup, proposed by Brian Julius:
let
Source = Excel.Workbook(File.Contents("C:UsersbrjulDownloadsEasy Excel Challenge 31st March 2024.xlsx"), null, true),
tblGrades_Table = Source{[Item="tblGrades",Kind="Table"]}[Data],
Students = Table.RemoveColumns( Excel.CurrentWorkbook(){[Name="tblStudents"]}[Content], "Grade"),
SubTable = Table.Distinct( Table.FromList(Students[Subject], Splitter.SplitByNothing(), {"Sub"}, null, ExtraValues.Error)),
AddMark = Table.ExpandListColumn( Table.AddColumn(SubTable, "Mark", each {0..100}), "Mark"),
Merge = Table.NestedJoin(AddMark, {"Sub", "Mark"}, tblGrades, {"Subject", "Marks From"}, "tblGrades", JoinKind.LeftOuter),
Expand = Table.ExpandTableColumn(Merge, "tblGrades", {"Grade"}, {"Grade"}),
AddAdjGrade = Table.FillDown( Table.AddColumn(Expand, "AdjGrade", each if [Mark] = 0 then "Fail" else [Grade]), {"AdjGrade"}),
Join = Table.Join( Students, {"Subject", "Marks"}, AddAdjGrade, {"Sub", "Mark"}),
Clean = Table.RenameColumns( Table.RemoveColumns(Join,{"Sub", "Mark", "Grade"}), {"AdjGrade", "Grade"})
in
CleanPower Query solution 6 for Approximate Lookup, proposed by Ramiro Ayala Chávez:
let
t2 = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
t1 = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
T = Table.TransformColumns,
A = Table.AddColumn,
a = Table.Group(t2, {"Subject"}, {"G", each [[Marks From], [Grade]]}),
b = T(
a,
{
"G",
each Table.InsertRows(
Table.InsertRows(_, 0, {[Marks From = 100, Grade = "A"]}),
Table.RowCount(_) + 1,
{[Marks From = 0, Grade = "Fail"]}
)
}
),
c = T(b, {"G", each Table.AddIndexColumn(_, "I")}),
d = A(
c,
"H",
each
let
x = [G],
y = A(x, "P", each try x[Marks From]{[I] - 1} otherwise null)
in
y
),
e = T(d, {"H", each A(_, "L", each try List.Reverse({[Marks From] .. [P] - 1}) otherwise null)})[
[Subject],
[H]
],
f = Table.ExpandListColumn(Table.ExpandTableColumn(e, "H", {"Grade", "L"}, {"Grade", "L"}), "L"),
g = Table.ReplaceValue(f, null, 100, Replacer.ReplaceValue, {"L"}),
m1 = A(t1, "M", each [Subject] & Text.From([Marks])),
m2 = A(g, "M", each [Subject] & Text.From([L])),
Sol = Table.RemoveColumns(A(m1, "Grade", each m2[Grade]{List.PositionOf(m2[M], [M])}), "M")
in
SolPower Query solution 7 for Approximate Lookup, proposed by Mahmoud Bani Asadi:
= Table.AddColumn(Source, "Grade", each List.First(Table.SelectRows(tblGrades,(IT)=>IT[Marks From]<=[Marks] and IT[Subject]=[Subject])[Grade])?? "Fail") Power Query solution 8 for Approximate Lookup, proposed by Mahmoud Bani Asadi:
let
Source = Excel.CurrentWorkbook(){[Name = "tblStudents"]}[Content],
Group = Table.Group(
Source,
{"Student"},
{
{
"tbl",
each [
a = Table.Combine(
{
_,
Table.RenameColumns(
Excel.CurrentWorkbook(){[Name = "tblGrades"]}[Content],
{{"Marks From", "Marks"}}
)
}
),
b = Table.Group(
a,
{"Subject"},
{
{
"tbl",
each Table.ReplaceValue(
Table.SelectRows(
Table.FillDown(Table.Sort(_, {{"Marks", Order.Ascending}}), {"Grade"}),
each [Student] <> null
),
null,
"Fail",
Replacer.ReplaceValue,
{"Grade"}
)
}
}
),
c = Table.SelectRows(
Table.ExpandTableColumn(b, "tbl", {"Marks", "Grade"}),
each [Marks] <> null
)
][c]
}
}
),
Expand = Table.ExpandTableColumn(Group, "tbl", {"Marks", "Grade"}, {"Marks", "Grade"})
in
ExpandPower Query solution 9 for Approximate Lookup, proposed by Nelson Mwangi:
let
tblGrades = Excel.CurrentWorkbook(){[Name = "tblGrades"]}[Content],
tblStudents = Excel.CurrentWorkbook(){[Name = "tblStudents"]}[Content],
Merge = Table.TransformColumns(
Table.NestedJoin(tblStudents, {"Subject"}, tblGrades, {"Subject"}, "NewColumn"),
{"NewColumn", each Table.AddIndexColumn(_, "Index", 1, 1)}
),
XpandMerge = Table.ExpandTableColumn(
Merge,
"NewColumn",
{"Marks From", "Grade", "Index"},
{"Marks From", "Grade", "Index"}
),
IFColumn = Table.AddColumn(
XpandMerge,
"Custom",
each
if [Marks] >= [Marks From] then
[Grade]
else if [Marks] < [Marks From] and [Index] = 5 then
"Fail"
else
"FALSE"
),
Filter = Table.SelectRows(IFColumn, each [Custom] <> "FALSE"),
Group = Table.RemoveColumns(
Table.Group(
Filter,
{"Student", "Subject", "Marks"},
{
{"Index", each List.Min([Index]), type number},
{"Grade", each List.Min([Custom]), type text}
}
),
{"Index"}
)
in
GroupPower Query solution 10 for Approximate Lookup, proposed by Mike “excelisfun” Girvin:
let m = [Marks], s = [Subject] in
List.First( Table.SelectRows( BufferedLT,
each [MarksFrom]<=m and [Subject]=s
)[Grade]
)??"F" )
Thanks for the cool Easter task, Crispo!!!!!!Solving the challenge of Approximate Lookup with Excel
Excel solution 1 for Approximate Lookup, proposed by Omid Motamedisedeh:
=INDEX(
{"A";
"B";
"C";
"D";
"E";
"Fail"},COUNTIFS(
$H$4:$H$24,C4,$G$4:$G$24,">="&D4)+1)Excel solution 2 for Approximate Lookup, proposed by Julian Poeltl:
=BYROW(tblStudents[[Subject]:[Marks]],
LAMBDA(ST,
LET(GT,
tblGrades,
IFERROR(TAKE(FILTER(CHOOSECOLS(
GT,
3),
(CHOOSECOLS(
GT,
2)=CHOOSECOLS(
ST,
1))*(CHOOSECOLS(
ST,
2)>=CHOOSECOLS(
GT,
1))),
1),
"Fail"))))
=MAP(tblStudents[Subject],
tblStudents[Marks],
LAMBDA(S,
M,
CHOOSEROWS(SORT(UNIQUE(IF((tblGrades[Marks From]<=M)*(tblGrades[Subject]=S),
tblGrades[Grade],
"Fail"))),
1)))Excel solution 3 for Approximate Lookup, proposed by Aditya Kumar Darak 🇮🇳:
=IFNA(
VLOOKUP(
1,
CHOOSE(
{1,
2},
COUNTIFS(
[@Subject],
tblGrades[Subject],
[@Marks],
">=" & tblGrades[Marks From]),
tblGrades[Grade]),
2,
0),
"Fail")Excel solution 4 for Approximate Lookup, proposed by Oscar Mendez Roca Farell:
=MAP(
C4:C12,
D4:D12,
LAMBDA(
c,
d,
IFNA(
VLOOKUP(
d,
SORT(
FILTER(
G4:I23,
H4:H23=c)),
3,
1),
"Fail")))Excel solution 5 for Approximate Lookup, proposed by Sunny Baggu:
=MAP(
tblStudents[Subject],
tblStudents[Marks],
LAMBDA(a,
b,
TAKE(
FILTER(
tblGrades[Grade],
(tblGrades[Subject] = a) * (tblGrades[Marks From] <= b),
"Fail"),
1)))Excel solution 6 for Approximate Lookup, proposed by Abdallah Ally:
=MAP(
tblStudents[Subject],
tblStudents[Marks],
LAMBDA(
x,
y,
LET(
a,
FILTER(
HSTACK(
tblGrades[Marks From],
tblGrades[Grade]),
tblGrades[Subject]=x),
XLOOKUP(
y,
TAKE(
a,
,
1),
TAKE(
a,
,
-1),
"Fail",
-1))))Excel solution 7 for Approximate Lookup, proposed by Ankur Sharma:
=MAP(C8:C16,
D8:D16,
LAMBDA(a,
b,
TAKE(FILTER(I8:I27,
(H8:H27 = a) * (G8:G27 <= b),
"Fail"),
1)))Excel solution 8 for Approximate Lookup, proposed by Ankur Sharma:
=IFERROR(
INDEX(
$I$135:$I$154,
MATCH(
C135,
IF(
tblGrades[MARKS FROM] <= D135,
tblGrades[SUBJECT],
""),
0)),
"Fail")Excel solution 9 for Approximate Lookup, proposed by JvdV –:
=IFNA(INDEX($I$4:$I$23,
MATCH(1,
(D4>=$G$4:$G$23)*($H$4:$H$23=C4),
0)),
"Fail")Excel solution 10 for Approximate Lookup, proposed by Owen Price:
=LET(
d,
FILTER(
tblGrades,
tblGrades[Subject]=[@Subject]),
XLOOKUP(
[@Marks],
TAKE(
d,
,
1),
TAKE(
d,
,
-1),
"Fail",
-1)
)Excel solution 11 for Approximate Lookup, proposed by Mey Tithveasna:
=IFNA(INDEX(tblGrades[Grade],
XMATCH(1,
(tblGrades[Marks From]<=[@Marks])*(tblGrades[Subject]=[@Subject]),
0)),
"Fail") =IFNA(
LOOKUP(
D4,
SORT(
FILTER(
tblGrades,
tblGrades[Subject]=tblStudents[@Subject]))),
"Fail")Excel solution 12 for Approximate Lookup, proposed by Milan Shrimali:
=LET(
list1,
SORT(
FILTER(
$G$4:$I$23,
$H$4:$H$23=$C4)),
1,
1),
list2,
FILTER(
list1,
{1,
0,
0}),
list3,
FILTER(
list1,
{0,
0,
1}),
IFNA(
LOOKUP(
$D4,
list2,
list3),
"FAIL"))Excel solution 13 for Approximate Lookup, proposed by Peter Bartholomew:
=MAP(
ResultTbl[Marks],
ResultTbl[Subject],
LAMBDA(
mk,
subj,
XLOOKUP(
mk,
IF(
GradingTbl[Subject] = subj,
GradingTbl[MarksFrom]),
GradingTbl[Grade],
"Fail",
-1
)
)
)
but,
if I were not allowed dynamic arrays,
one could rely on the formula fill down of the results table and reduce it to
=XLOOKUP(
[@Marks],
IF(
GradingTbl[Subject] = [@Subject],
GradingTbl[MarksFrom]),
GradingTbl[Grade],
"Fail",
-1
)Excel solution 14 for Approximate Lookup, proposed by Mahmoud Bani Asadi:
=IFNA(
LOOKUP(
tblStudents[@Marks],
IF(
tblStudents[@Subject]=tblGrades[Subject],
tblGrades,
"")),
"Fail")Excel solution 15 for Approximate Lookup, proposed by Mahmoud Bani Asadi:
=IFNA(
LOOKUP(
tblStudents[@Marks],
SORT(
FILTER(
tblGrades,
tblGrades[Subject]=tblStudents[@Subject]))),
"Fail")Excel solution 16 for Approximate Lookup, proposed by Sergei Baklan:
=IFERROR(
INDEX( tblGrades[Grade],
AGGREGATE( 15,
6,
ROW(
tblGrades[Grade]) /
(tblGrades[Subject] = [@Subject]) /
(tblGrades[Marks From] <= [@Marks]),
1) - ROW(
tblGrades[
hashtag
#Headers])),
"Fail")Excel solution 17 for Approximate Lookup, proposed by Hazem Hassan:
=LET(
rng,
H4:J23,
MAP(
C4:C12,
D4:D12,
LAMBDA(
x,
y,
IFNA(
VLOOKUP(
y,
SORT(
FILTER(
rng,
x = INDEX(
rng,
,
2))),
3),
"fail"))))Excel solution 18 for Approximate Lookup, proposed by Gabriel Raigosa:
=MAP(
tblStudents[Subject],
tblStudents[Marks],
LAMBDA(
s,
m,
IFERROR(
VLOOKUP(
m,
SORT(
FILTER(
tblGrades,
tblGrades[Subject]=s)),
3),
"Fail")))