Lookup correct exchange rate & calculate Amt To. We have different half-year rates. Dynamic array function allowed, but Extra marks for Legacy solutions or PowerQuery Solution
📌 Challenge Details and Links
Challenge Number: 25
Challenge Difficulty: ⭐⭐
📥Link to the solutions on LinkedIn
Solving the challenge of Multiple Tables Lookup with Power Query
Power Query solution 1 for Multiple Tables Lookup, proposed by Omid Motamedisedeh:
let
Tables = Excel.CurrentWorkbook()[Content],
Results = Table.AddColumn(
Tables{0},
"Amt",
each Record.Field(Tables{if Date.Month([Date]) < 7 then 1 else 2}{[Column1 = _[From]]}, _[To])
* [Amt From]
)
in
ResultsPower Query solution 2 for Multiple Tables Lookup, proposed by Aditya Kumar Darak 🇮🇳:
let
data = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
JTJ = Excel.CurrentWorkbook(){[Name = "JTJ"]}[Content],
JTD = Excel.CurrentWorkbook(){[Name = "JTD"]}[Content],
Return = Table.AddColumn(
data,
"Answer",
each [
M = Date.Month([Date]),
TF = M < 7,
T = if TF then JTJ else JTD,
V = Record.Field(T{[Column1 = [From]]}, [To]),
R = V * [Amt From]
][R]
)
in
ReturnPower Query solution 3 for Multiple Tables Lookup, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
J2J = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
J2D = Excel.CurrentWorkbook(){[Name = "Table3"]}[Content],
Sol = Table.AddColumn(
Source,
"Amt To",
(x) =>
let
a = if Date.Month(x[Date]) < 7 then J2J else J2D,
b = Table.SelectRows(a, each x[From] = [Column1]),
c = List.PositionOf(Table.ColumnNames(b), x[To]),
d = Table.ToColumns(b){c}{0} * x[Amt From]
in
d
)
in
SolSolving the challenge of Multiple Tables Lookup with Excel
Excel solution 1 for Multiple Tables Lookup, proposed by Rick Rothstein:
=E3*INDEX(OFFSET(J$5:M$8,
8*(MONTH(
B3)>6),
0),
MATCH(
C3,
I$5:I$8,
0),
MATCH(
D3,
J$4:M$4,
0))Excel solution 2 for Multiple Tables Lookup, proposed by Kris Jaganah:
=LET(
a,
B3:B17,
b,
C3:C17,
c,
D3:D17,
d,
E3:E17,
e,
I4:M8,
f,
I12:M16,
d*MAP(
b,
c,
a,
LAMBDA(
x,
y,
z,
IF(
MONTH(
z)<7,
XLOOKUP(
x,
TAKE(
e,
,
1),
XLOOKUP(
y,
TAKE(
e,
1),
e)),
XLOOKUP(
x,
TAKE(
f,
,
1),
XLOOKUP(
y,
TAKE(
f,
1),
f))))))Excel solution 3 for Multiple Tables Lookup, proposed by Julian Poeltl:
=LET(
T,
B2:E17,
Xo,
J4:N8,
Xt,
J12:N16,
TT,
DROP(
T,
1),
M,
MONTH(
TAKE(
TT,
,
1))<7,
F,
CHOOSECOLS(
TT,
2),
To,
CHOOSECOLS(
TT,
3),
MAP(
M,
F,
To,
LAMBDA(
M,
F,
T,
LET(
Ta,
IF(
M,
Xo,
Xt),
INDEX(
Ta,
XMATCH(
F,
CHOOSECOLS(
Ta,
1)),
XMATCH(
T,
CHOOSEROWS(
Ta,
1))))))*DROP(
TAKE(
T,
,
-1),
1))Excel solution 4 for Multiple Tables Lookup, proposed by Aditya Kumar Darak 🇮🇳:
=MAP(
B3:B17,
C3:C17,
D3:D17,
E3:E17,
LAMBDA(
a,
b,
c,
d,
XLOOKUP(
b,
J5:J8,
XLOOKUP(
c,
K4:N4,
IF(
MONTH(
a) < 7,
K5:N8,
K13:N16))) * d))Excel solution 5 for Multiple Tables Lookup, proposed by Oscar Mendez Roca Farell:
=E3*INDEX((K$5:N$8,
K$13:N$16),
MATCH(
C3; J$5:J$8; ); MATCH(
D3; K$4:N$4; ); 1+(MONTH(
B3)>6))
M365:
=E3:E17*INDEX(K5:N16,
XMATCH(C3:C17,
J5:J16,
,
-1^(MONTH(
B3:B17)>6)),
XMATCH(
D3:D17,
K4:N4))Excel solution 6 for Multiple Tables Lookup, proposed by Sunny Baggu:
=MAP(
B3:B17,
C3:C17,
D3:D17,
E3:E17,
LAMBDA(a,
b,
c,
d,
SUM(
d * (I5:I8 = b) * (J4:M4 = c) *
IF(
MONTH(
a) < 7,
J5:M8,
J13:M16))))Excel solution 7 for Multiple Tables Lookup, proposed by Ankur Sharma:
=INDEX(($J$5:$M$8,
$J$13:$M$16),
MATCH(
C3,
$I$5:$I$8,
0),
MATCH(
D3,
$J$4:$M$4,
0),
IF(
MONTH(
B3) < 7,
1,
2))Excel solution 8 for Multiple Tables Lookup, proposed by JvdV -:
=SUMPRODUCT((I$5:I$8=C3)*(J$4:M$4=D3)*E3*IF(
MONTH(
B3)>6,
J$13:M$16,
J$5:M$8))