Find the value corresponding to numbers given in column N from the grid given of left hand side. First digit of number is in column B, second digit of number is in row 2, 3rd digit in column B, 4th digit in row 2 and this keeps repeating. Hence, you need to find the number for last 2 digits only. Second last digit can be a row or column and last digit can be column or row. Hence for 138 => 1=> row, 3=>column corresponding to number 3 and 8=> row corresponding to number 8. Intersection of these columns and rows are 708 for 2938 => 2=>row, 9=> column, 3=> row corresponding to number 3 and 8=> column corresponding to number 8. Intersection of these columns and rows are 743.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 683
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Grid Lookup By Pattern with Power Query
Power Query solution 1 for Grid Lookup By Pattern, proposed by Kris Jaganah:
let
A = (x) => Excel.CurrentWorkbook(){[Name = "Table" & x]}[Content],
B = Table.ToColumns(A("1")),
C = List.Transform(
A("2")[Number],
each [
a = Text.From(_),
b = Text.Length(a),
c = List.Transform(Text.ToList(Text.End(a, 2)), Number.From),
d = List.Skip(B),
e = if Number.IsOdd(b) then d{c{0}}{c{1}} else d{c{1}}{c{0}}
][e]
)
in
CPower Query solution 2 for Grid Lookup By Pattern, proposed by Ramiro Ayala Chávez:
let
G = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
N = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
TAC = Table.AddColumn,
TR = Text.Range,
TL = Text.Length,
a = List.Transform(Table.ToRows(G), List.Skip),
b = Table.TransformColumnTypes(N, {"Number", type text}),
c = TAC(b, "B", each Number.IsOdd(TL([Number]))),
d = TAC(
c,
"R",
each if [B] = true then TR([Number], TL([Number]) - 1, 1) else TR([Number], TL([Number]) - 2, 1)
),
e = TAC(
d,
"C",
each if [B] = true then TR([Number], TL([Number]) - 2, 1) else TR([Number], TL([Number]) - 1, 1)
)[[R], [C]],
f = Table.ToRows(e),
Fx = (x) =>
let
g = a{Number.From(x{0})}{Number.From(x{1})}
in
g,
Sol = Table.FromColumns({List.Transform(f, each Fx(_))}, {"Value"})
in
SolPower Query solution 3 for Grid Lookup By Pattern, proposed by Meganathan Elumalai:
let
Source = each Excel.CurrentWorkbook(){[Name = _]}[Content],
Result = Table.AddColumn(
Source("Table2"),
"Result",
each [
txt = Text.From([Number]),
Lst = List.Transform(List.LastN(Text.ToList(Text.From([Number])), 2), Number.From),
fx = (x, y) => List.Skip(Table.ToColumns(Source("Table1"))){Lst{x}}{Lst{y}},
fin = if Number.IsOdd(Text.Length(txt)) then fx(0, 1) else fx(1, 0)
][fin]
)
in
ResultPower Query solution 4 for Grid Lookup By Pattern, proposed by Peter Krkos:
PowerQuery solution:
= Table.AddColumn(N, "Value", each
[ a = Text.ToList(Text.From([Number])),
b = if Number.IsEven(List.Count(a)) then List.LastN(a, 2) else List.Reverse(List.LastN(a, 2)),
c = Record.ToList(Grid{Number.From(b{0})}){Number.From(b{1})}
][c], Int64.Type)
Power Query solution 5 for Grid Lookup By Pattern, proposed by Peter Krkos:
let
Grid = Table.FromRows(
List.Split(
{
780,
253,
334,
915,
221,
390,
304,
575,
642,
600,
335,
356,
125,
889,
759,
572,
910,
540,
362,
630,
928,
833,
850,
193,
602,
108,
175,
745,
573,
948,
340,
250,
714,
602,
301,
782,
470,
320,
743,
788,
812,
591,
285,
929,
780,
703,
902,
290,
765,
355,
468,
472,
336,
449,
738,
226,
649,
465,
393,
136,
944,
717,
451,
463,
688,
921,
244,
171,
315,
721,
689,
828,
808,
549,
411,
687,
553,
119,
378,
137,
834,
294,
230,
708,
403,
272,
788,
391,
650,
546,
503,
266,
151,
751,
145,
263,
494,
774,
720,
797
},
10
),
{"0" .. "9"}
),
N = Table.FromColumns(
{{100, 55, 298, 854, 1991, 559, 20195, 82, 637, 97368012}},
type table [Number = Int64.Type]
),
Ad_Value = Table.AddColumn(
N,
"Value",
each [
a = Text.ToList(Text.From([Number])),
b = if Number.IsEven(List.Count(a)) then List.LastN(a, 2) else List.Reverse(List.LastN(a, 2)),
c = Record.ToList(Grid{Number.From(b{0})}){Number.From(b{1})}
][c],
Int64.Type
)
in
Ad_ValuePower Query solution 6 for Grid Lookup By Pattern, proposed by Aleksandar Kovacevic:
let
Source = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
Fx = each [
a = Text.ToList(try Number.ToText([Number]) otherwise [Number]),
b = List.Count(a),
r = Number.From(if Number.IsOdd(b) then a{b - 2} else a{b - 1}),
c = Number.From(if Number.IsEven(b) then a{b - 2} else a{b - 1}),
d = Record.ToTable(Table1{c})[Value]{r}
][d],
Res = Table.AddColumn(Source, "Value", Fx)
in
ResPower Query solution 7 for Grid Lookup By Pattern, proposed by Le Ngoc Tinh:
let
tbl1 = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
tbl2 = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
up = Table.ToRows(Table.UnpivotOtherColumns(tbl1, {"Ro"}, "Col", "V")),
Re = Table.AddColumn(
tbl2,
"Value",
each
let
n = Text.ToList(Text.From([Number])),
ll = List.LastN(n, 2),
ln = if Number.IsEven(List.Count(n)) then ll else List.Reverse(ll)
in
List.Last(List.Select(up, (y) => y{0} = Number.From(ln{0}) and y{1} = ln{1})){2}
)[Value]
in
ReSolving the challenge of Grid Lookup By Pattern with Excel
Excel solution 1 for Grid Lookup By Pattern, proposed by Bo Rydobon 🇹🇭:
=LET(n,N3:N12,l,LEN(n),y,ISODD(l),INDEX(C3:L12,MID(n,l-1+y,1)+1,MID(n,l-y,1)+1))Excel solution 2 for Grid Lookup By Pattern, proposed by Rick Rothstein:
=MAP(N3:N12,LAMBDA(x,LET(d,C3:L12,t,1+LEFT(MOD(x,100)),u,1+RIGHT(x),IF(ISODD(LEN(x)),INDEX(d,u,t),INDEX(d,t,u)))))Excel solution 3 for Grid Lookup By Pattern, proposed by Rick Rothstein:
=LET(n,N3:N12,r,RIGHT(n,2),v,MID(r&r,1+ISODD(LEN(n)),2),INDEX(C3:L12,1+LEFT(v),1+RIGHT(v)))Excel solution 4 for Grid Lookup By Pattern, proposed by John V.:
=LET(x,N3:N12,n,LEN(x),i,ISODD(n),INDEX(C3:L12,1+MID(x,n+i-1,1),1+MID(x,n-i,1)))Excel solution 5 for Grid Lookup By Pattern, proposed by Kris Jaganah:
=MAP(
N3:N12,
LAMBDA(
x,
LET(
a,
LEN(
x
),
b,
RIGHT(
x
)+1,
c,
MID(
x,
a-1,
1
)+1,
d,
C3:L12,
e,
INDEX,
IF(
ISEVEN(
a
),
e(
d,
c,
b
),
e(
d,
b,
c
)
)
)
)
)Excel solution 6 for Grid Lookup By Pattern, proposed by Julian Poeltl:
=LET(
I,
N3:N12,
O,
RIGHT(
I,
1
)+1,
T,
LEFT(
RIGHT(
I,
2
),
1
)+1,
INDEX(
C3:L12,
IF(
ISEVEN(
LEN(
I
)
),
T,
O
),
IF(
ISEVEN(
LEN(
I
)
),
O,
T
)
)
)Excel solution 7 for Grid Lookup By Pattern, proposed by Timothée BLIOT:
=LET(
A,
C3:L12,
B,
N3:N12,
D,
RIGHT(
B
)+1,
E,
LEFT(
RIGHT(
B,
2
)
)+1,
IF(
ISODD(
LEN(
B
)
),
INDEX(
A,
D,
E
),
INDEX(
A,
E,
D
)
)
)Excel solution 8 for Grid Lookup By Pattern, proposed by Hussein SATOUR:
=MAP(N3:N12,LAMBDA(x,XLOOKUP(RIGHT(x,2),TEXT(SEQUENCE(100,,0),"00"),IF(ISEVEN(LEN(x)),TOCOL(C3:L12),TOCOL(C3:L12,,1)))))Excel solution 9 for Grid Lookup By Pattern, proposed by Oscar Mendez Roca Farell:
=LET(
n,
N3:N12,
e,
LEN(
n
),
a,
MID(
n,
e-1,
1
)+1,
b,
MID(
n,
e,
1
)+1,
IF(
MOD(
e,
2
),
INDEX(
C3:L12,
b,
a
),
INDEX(
C3:L12,
a,
b
)
)
)Excel solution 10 for Grid Lookup By Pattern, proposed by Duy Tùng:
=MAP(
N3:N12,
LAMBDA(
x,
LET(
a,
RIGHT(
x,
2
),
INDEX(
IF(
ISEVEN(
LEN(
x
)
),
TRANSPOSE(
C3:L12
),
C3:L12
),
RIGHT(
a
)+1,
LEFT(
a
)+1
)
)
)
)Excel solution 11 for Grid Lookup By Pattern, proposed by Sunny Baggu:
=LET(
_a,
RIGHT(
N3:N12,
2
),
_b,
LEFT(
_a
) + 0,
_d,
RIGHT(
_a
) + 0,
_l,
LEN(
N3:N12
),
_m,
MOD(
_l,
2
),
_r,
IF(
_m,
_d,
_b
),
_c,
IF(
_m,
_b,
_d
),
MAP(
_r & _c,
LAMBDA(
k,
TOCOL(
IF(
B3:B12 & C2:L2 = k,
C3:L12,
1 / x
),
3
)
)
)
)Excel solution 12 for Grid Lookup By Pattern, proposed by LEONARD OCHEA 🇷🇴:
=LET(
n,
N3:N12,
l,
LEN(
n
),
b,
ISEVEN(
l
),
M,
MID,
F,
LAMBDA(
x,
IF(
x,
M(
n,
l-1,
1
),
M(
n,
l,
1
)
)+1
),
INDEX(
C3:L12,
F(
b
),
F(
b-1
)
)
)Excel solution 13 for Grid Lookup By Pattern, proposed by Anshu Bantra:
=LET(
data_, C3:L12,
nums_, N3:N12,
num_len_, LEN(nums_),
row_, RIGHT(nums_)+1,
col_, MID(nums_, num_len_-1, 1)+1,
ans_, IF( ISODD(num_len_), INDEX(data_,row_, col_), INDEX(data_, col_, row_)),
HSTACK(nums_, ans_)
)Excel solution 14 for Grid Lookup By Pattern, proposed by Anshu Bantra:
= str(num)
r, c = int(num[-1]), int(num[-2])
if len(num) % 2 == 0:
return data[c,r]
else:
return data[r,c]
data = to_df(REF("C2:L12")).values
df = to_df(REF("N2:N12"))
df['Value'] = df['Number'].apply(get_arr_data)Excel solution 15 for Grid Lookup By Pattern, proposed by Md. Zohurul Islam:
=LET(u,C3:L12,v,N3:N12,
MAP(v,LAMBDA(x,LET(
sq,SEQUENCE(LEN(x)),
md,MOD(sq,2),
a,--MID(x,sq,1),
b,TAKE(FILTER(a,md<>0),-1)+1,
c,TAKE(FILTER(a,md=0),-1)+1,
d,INDEX(u,b,c),
d))))Excel solution 16 for Grid Lookup By Pattern, proposed by Pieter de B.:
=LET(
n,
N3:N12,
g,
C3:L12,
l,
LEN(
n
),
i,
INDEX,
m,
MID(
n,
l-{0,
1},
1
)+1,
IF(
ISODD(
l
),
i(
g,
i(
m,
,
1
),
i(
m,
,
2
)
),
i(
g,
i(
m,
,
2
),
i(
m,
,
1
)
)
)
)Excel solution 17 for Grid Lookup By Pattern, proposed by ferhat CK:
=MAP(
N3:N12,
LAMBDA(
x,
LET(
a,
--REGEXEXTRACT(
x,
".",
1
),
b,
MOD(
SEQUENCE(
,
COLUMNS(
& a
)
),
2
),
t,
TAKE,
say,
t(
a,
,
-2
),
i,
t(
b,
,
-2
),
INDEX(
C3:L12,
IF(
t(
i,
,
1
)=1,
t(
say,
,
1
)+1,
t(
say,
,
-1
)+1
),
IF(
t(
i,
,
1
)=1,
t(
say,
,
-1
)+1,
t(
say,
,
1
)+1
)
)
)
)
)Excel solution 18 for Grid Lookup By Pattern, proposed by Jaroslaw Kujawa:
=MAP(N3:N12;LAMBDA(z;LET(L;LEN(z);M;MATCH;I;INDEX;D;MID;T;C3:L12;a;M(--D(z;L-1;1);B3:B12;);b;M(--D(z;L;1);C2:L2;);IF(ISODD(L);I(T;b;a);I(T;a;b)))))Excel solution 19 for Grid Lookup By Pattern, proposed by Jaroslaw Kujawa:
=BYROW(
N3:N12;
LAMBDA(
x;
LET(
p;
RIGHT(
x;
2
);
k;
TEXTSPLIT(
CONCAT(
TOCOL(
C2:L2&B3:B12&";"&C3:L12&"|"
)
);
";";
"|"
);
INDEX(
k;
MATCH(
IF(
ISODD(
LEN(
x
)
);
p;
RIGHT(
p
)&LEFT(
p
)
);
TAKE(
k;
;
1
);
);
2
)
)
)
)Excel solution 20 for Grid Lookup By Pattern, proposed by Ankur Sharma:
=IF(MOD(LEN(N3:N12), 2) = 0,
XLOOKUP(RIGHT(N3:N12, 2), TOCOL(B3:B12 & C2:L2), TOCOL(C3:L12)),
XLOOKUP(RIGHT(N3:N12, 2), TOCOL(B3:B12 & C2:L2), TOCOL(TRANSPOSE(C3:L12))))Excel solution 21 for Grid Lookup By Pattern, proposed by Meganathan Elumalai:
=MAP(N3:N12,LAMBDA(x,XLOOKUP(RIGHT(x,2),TOCOL(B3:B12&C2:L2),TOCOL(C3:L12,,ISODD(LEN(x))))))Excel solution 22 for Grid Lookup By Pattern, proposed by Mihai Radu O:
=LET(
a,
N3:N12,
b,
RIGHT(
a,
2
),
r,
--IF(
ISEVEN(
LEN(
a
)
),
LEFT(
b
),
RIGHT(
b
)
),
c,
--IF(
ISEVEN(
LEN(
a
)
),
RIGHT(
b
),
LEFT(
b
)
),
INDEX(
B2:L12,
XMATCH(
r,
B2:B12
),
XMATCH(
c,
B2:L2
)
)
)Excel solution 23 for Grid Lookup By Pattern, proposed by Maciej Kopczyński:
=LET(
numbersToCheck,
N3:N12,
seq,
B3:B12,
map,
C3:L12,
last2Digits,
MID(
RIGHT(
numbersToCheck,
2
),
{1,
2},
1
)+0,
RowStart,
ISEVEN(
LEN(
numbersToCheck
)
),
rows,
CHOOSECOLS(
MATCH(
last2Digits,
seq,
0
),
1
),
cols,
CHOOSECOLS(
MATCH(
last2Digits,
seq,
0
),
2
),
result,
INDEX(
map,
IF(
RowStart,
rows,
cols
),
IF(
RowStart,
cols,
rows
)
),
result
)Excel solution 24 for Grid Lookup By Pattern, proposed by Erdit Qendro:
=LET(inp,N3:N12,ln,LEN(inp),
md,MOD(ln,2)=1,
cllv,LEFT(RIGHT(inp,IF(md,2,1)))+0,
rwlv,LEFT(RIGHT(inp,IF(md,1,2)))+0,
rs,BYROW(CHAR(cllv+2+65)&(rwlv+3),
LAMBDA(r,INDIRECT(@r))),
rs)Excel solution 25 for Grid Lookup By Pattern, proposed by LUIS FLORENTINO COUTO CORTEGOSO:
=LET(
f,
LAMBDA(
n,
LET(
E,
MID,
l,
LEN(
n
),
p,
--ISEVEN(
l
),
INDEX(
C3:L12,
E(
n,
l-p,
1
)+1,
E(
n,
l-1+p,
1
)+1
)
)
),
MAP(
N3:N12,
f
)
)Excel solution 26 for Grid Lookup By Pattern, proposed by Fredson Alves Pinho:
=MAP(
N3:N12,
LAMBDA(
n,
LET(
dgts,
TAKE(
REGEXEXTRACT(
n,
".",
1
),
,
-2
),
INDEX(
INDEX(
C3:L12,
TOCOL(
dgts
)+1,
dgts+1
),
ISODD(
LEN(
n
)
)+1,
ISEVEN(
LEN(
n
)
)+1
)
)
)
)Excel solution 27 for Grid Lookup By Pattern, proposed by Ricardo Alexis Domínguez Hernández:
=LET(m,$C$3:$L$12,r,RIGHT(N3:N12,2),a,(RIGHT(r)*1)+1,b,(LEFT(r)*1)+1,
IF(ISODD(LEN(N3:N12))=TRUE,INDEX(m,a,b),INDEX(m,b,a)))Excel solution 28 for Grid Lookup By Pattern, proposed by Ricardo Romero Garcia:
=LET(
m;
N3:N12;
p;
ES.PAR(
LARGO(
m
)
);
f;
LAMBDA(
a;
EXTRAE(
m;
LARGO(
m
)-a;
1
)
);
b;
f(
1
);
c;
f(
0
);
fi;
SI(
p;
b;
c
);
co;
SI(
p;
c;
b
);
INDICE(
C3:L12;
fi+1;
co+1
)
)Excel solution 29 for Grid Lookup By Pattern, proposed by Coby Dombrowsky:
=MAP(N3:N12,LAMBDA(a,LET(l,LEN(a),p,MID(a,l-1,1)+1,u,RIGHT(a)+1,INDEX(C3:L12,IF(ISEVEN(l),p,u),IF(ISODD(l),p,u)))))Solving the challenge of Grid Lookup By Pattern with Python
Python solution 1 for Grid Lookup By Pattern, proposed by Konrad Gryczan, PhD:
import pandas as pd
import numpy as np
path = "683 Find Numbers in a Grid.xlsx"
input1 = pd.read_excel(path, usecols="C:L", skiprows=2, nrows=10, header=None).values
input2 = pd.read_excel(path, usecols="N", skiprows=1, nrows=11)
test = pd.read_excel(path, usecols="O", skiprows=1, nrows=11).assign(Value=lambda df: pd.to_numeric(df.iloc[:, 0]))
result = input2.assign(
num=lambda df: df.iloc[:, 0].astype(str),
n=lambda df: df['num'].str.len(),
mod=lambda df: df['n'] % 2,
row=lambda df: np.where(df['mod'] == 0, df['num'].str[-2:-1], df['num'].str[-1:]).astype(int) + 1,
col=lambda df: np.where(df['mod'] == 0, df['num'].str[-1:], df['num'].str[-2:-1]).astype(int) + 1
).assign(
Value=lambda df: [input1[row-1, col-1] for row, col in zip(df['row'], df['col'])]
).loc[:, ['Value']]
print(result.equals(test)) # True
Solving the challenge of Grid Lookup By Pattern with Python in Excel
Python in Excel solution 1 for Grid Lookup By Pattern, proposed by Aditya Kumar Darak 🇮🇳:
g = xl("C3:L12", False).values
df = xl("N2:N12", True)
def MyFun(n):
d = [int(x) for x in str(n)]
if len(d) < 2:
return None
r, c = d[0], d[1]
for i in range(2, len(d)):
if i % 2 == 0:
r = d[i]
else:
c = d[i]
return g[r, c]
df["Value"] = df["Number"].apply(MyFun)
df
Solving the challenge of Grid Lookup By Pattern with R
R solution 1 for Grid Lookup By Pattern, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/683 Find Numbers in a Grid.xlsx"
input1 = read_excel(path, range = "C3:L12", col_names = FALSE) %>% as.matrix()
input2 = read_excel(path, range = "N2:N12")
test = read_excel(path, range = "O2:O12") %>% mutate(Value = as.numeric(Value))
result = input2 %>%
mutate(
num = as.character(Number),
n = nchar(num),
mod = n %% 2,
row = as.numeric(ifelse(mod == 0, str_sub(num, n-1, n-1), str_sub(num, n, n)))+1,
col = as.numeric(ifelse(mod == 0, str_sub(num, n, n), str_sub(num, n-1, n-1)))+1) %>%
mutate(value =map2_dbl(row, col, ~input1[.x, .y])) %>%
select(value)
all.equal(result, test, check.attributes = FALSE)
# [1] TRUE
&&