Look up the Words (in column D) in Column1 and list the Result from Column2. A word can be found in more than 1 Column1 entries. If more than one word is given, then all words must be found in Column1. Note – Order of Words are not important.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 599
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Match Words in Column Data with Power Query
Power Query solution 1 for Match Words in Column Data, proposed by Kris Jaganah:
let
A = (z) => Excel.CurrentWorkbook(){[Name = z]}[Content],
B = Table.AddColumn(
A("Table2"),
"Result",
each Text.Combine(
List.Last(
List.Transform(
Text.Split([Words], ", "),
(y) =>
Table.SelectRows(
A("Table1"),
(x) => Text.Contains(x[Column1], y, Comparer.OrdinalIgnoreCase)
)[Column2]
)
),
", "
)
)
in
BPower Query solution 2 for Match Words in Column Data, proposed by Aditya Kumar Darak 🇮🇳:
let
Data = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Lower = Table.TransformColumns(Data, {"Column1", Text.Lower}),
Criteria = Excel.CurrentWorkbook(){[Name = "criteria"]}[Content],
Return = Table.AddColumn(
Criteria,
"Result",
each [
W = Text.Split([Words], ", "),
S = List.Transform(W, (f) => Table.ToRecords(Table.FindText(Lower, f))),
I = List.Intersect(S),
C = List.Transform(I, (f) => f[Column2]),
R = Text.Combine(C, ", ")
][R]
)
in
ReturnPower Query solution 3 for Match Words in Column Data, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Words = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
Lista = Table.AddColumn(Words, "A", each Text.Split([Words], ", "))[A],
Texto = Table.ToRows(Source),
Pos = List.Transform(
Lista,
each List.PositionOf(
List.Transform(
Texto,
(x) =>
List.AllTrue(List.Transform(_, (y) => Text.Contains(x{0}, y, Comparer.OrdinalIgnoreCase)))
),
true,
2
)
),
Sol = List.Transform(Pos, each Text.Combine(List.Transform(_, (x) => Source[Column2]{x}), ", "))
in
SolPower Query solution 4 for Match Words in Column Data, proposed by Abdallah Ally:
let
Source = each Excel.CurrentWorkbook(){[Name = _]}[Content],
AddCol = Table.AddColumn(
Source("Table2"),
"My Result",
each Text.Combine(
Table.SelectRows(
Source("Table1"),
(x) =>
List.ContainsAll(
Text.Split(x[Column1], " "),
Text.Split([Words], ", "),
Comparer.OrdinalIgnoreCase
)
)[Column2],
", "
)
),
Result = Table.AddColumn(AddCol, "Check", each [Result] = [My Result])
in
ResultPower Query solution 5 for Match Words in Column Data, proposed by Abdallah Ally:
let
Source = each Excel.CurrentWorkbook(){[Name = _]}[Content],
AddCol = Table.AddColumn(
Source("Table2"),
"My Result",
each Text.Combine(
Table.SelectRows(
Source("Table1"),
(x) =>
List.ContainsAll(
List.Transform(Text.Split(x[Column1], " "), Text.Lower),
Text.Split([Words], ", ")
)
)[Column2],
", "
)
),
Result = Table.AddColumn(AddCol, "Check", each [Result] = [My Result])
in
ResultPower Query solution 6 for Match Words in Column Data, proposed by Mihai Radu O:
let
t1 = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
t2 = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
r = Table.AddColumn(
t2,
"Result",
(t2) =>
Text.Combine(
Table.SelectRows(
t1,
(t1) =>
List.ContainsAll(
Text.Split(t1[Column1], " "),
Text.Split(t2[Words], ", "),
Comparer.OrdinalIgnoreCase
)
)[Column2],
", "
)
)
in
rPower Query solution 7 for Match Words in Column Data, proposed by Sergei Baklan:
let
sdata = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
data = Table.PromoteHeaders(sdata, [PromoteAllScalars = true]),
words = Excel.CurrentWorkbook(){[Name = "Words"]}[Content],
Headers = Table.PromoteHeaders(words, [PromoteAllScalars = true]),
Result = Table.AddColumn(
Headers,
"Result",
each Text.Combine(
Table.SelectRows(
data,
(t) => List.ContainsAll(Text.Split(Text.Lower(t[Column1]), " "), Text.Split([Words], ", "))
)[Column2],
", "
)
)
in
ResultSolving the challenge of Match Words in Column Data with Excel
Excel solution 1 for Match Words in Column Data, proposed by Bo Rydobon 🇹🇭:
"b"&TEXTSPLIT(
x,
", "
)&"b"Excel solution 2 for Match Words in Column Data, proposed by Bo Rydobon 🇹🇭:
=MAP(D3:D7,LAMBDA(x,ARRAYTOTEXT(FILTER(B3:B11,BYROW(REGEXTEST(A3:A11,"b"&TEXTSPLIT(x,", ")&"b",1),AND)))))Excel solution 3 for Match Words in Column Data, proposed by Rick Rothstein:
=MAP(
D3:D7,
LAMBDA(
d,
LET(
w,
TEXTSPLIT(
d,
", "
),
REDUCE(
"",
w,
LAMBDA(
a,
x,
TEXTJOIN(
", ",
,
FILTER(
B3:B11,
ISNUMBER(
SEARCH(
" "&x&" ",
" "&A3:A11&" "
)
)
)
)
)
)
)
)
)Excel solution 4 for Match Words in Column Data, proposed by John V.:
=MAP(
D3:D7,
LAMBDA(
x,
ARRAYTOTEXT(
FILTER(
B3:B11,
BYROW(
1-ISERR(
SEARCH(
TEXTSPLIT(
x,
", "
),
A3:A11
)
),
AND
)
)
)
)
)Excel solution 5 for Match Words in Column Data, proposed by 🇰🇷 Taeyong Shin:
=MAP(D3:D7,LAMBDA(x,ARRAYTOTEXT(FILTER(B3:B11,REGEXTEST(A3:A11,CONCAT("(?=.*"&TEXTSPLIT(x,", ")&")"),1)))))Excel solution 6 for Match Words in Column Data, proposed by Kris Jaganah:
=HSTACK(D3:D7,MAP(D3:D7,LAMBDA(x,LET(a,TEXTSPLIT(x,", "),ARRAYTOTEXT(FILTER(B3:B11,COUNTA(a)=BYROW(IFERROR(SEARCH(a,A3:A11),""),COUNT)))))))Excel solution 7 for Match Words in Column Data, proposed by Julian Poeltl:
=MAP(
D3:D7,
LAMBDA(
A,
TEXTJOIN(
", ",
,
FILTER(
B3:B11,
ISNUMBER(
BYROW(
SEARCH(
TEXTSPLIT(
A,
", "
),
A3:A11
),
LAMBDA(
A,
PRODUCT(
A
)
)
)
)
)
)
)
)Excel solution 8 for Match Words in Column Data, proposed by Aditya Kumar Darak 🇮🇳:
=MAP(
D3:D7,
LAMBDA(a,
LET(
split, TEXTSPLIT(a, ", "),
find, ISNUMBER(SEARCH(" " & split & " ", " " & A3:A11 & " ")),
check, BYROW(find, AND),
rtrn, ARRAYTOTEXT(FILTER(B3:B11, check)),
rtrn
)
)
)Excel solution 9 for Match Words in Column Data, proposed by Timothée BLIOT:
=MAP(
G3:G7,
LAMBDA(
z,
ARRAYTOTEXT(
FILTER(
B3:B11,
REDUCE(
1,
TEXTSPLIT(
z,
", "
),
LAMBDA(
w,
v,
w*--ISNUMBER(
SEARCH(
v,
A3:A11
)
)
)
)
)
)
)
)Excel solution 10 for Match Words in Column Data, proposed by Sunny Baggu:
=MAP(
D3:D7,
LAMBDA(
t,
ARRAYTOTEXT(
FILTER(
B3:B11,
ISNUMBER(
BYROW(
SEARCH(
TEXTSPLIT(
t,
", "
),
A3:A11
),
LAMBDA(
a,
SUM(
a
)
)
)
)
)
)
)
)Excel solution 11 for Match Words in Column Data, proposed by LEONARD OCHEA 🇷🇴:
=MAP(
D3:D7,
LAMBDA(
x,
TEXTJOIN(
", ",
,
IF(
BYROW(
REGEXTEST(
A3:A11,
TEXTSPLIT(
x,
", "
),
1
),
AND
),
B3:B11,
""
)
)
)
)Excel solution 12 for Match Words in Column Data, proposed by Md. Zohurul Islam:
=LET(
p,
D3:D7,
q,
A3:A11,
r,
B3:B11,
s,
MAP(
p,
LAMBDA(
x,
LET(
a,
ABS(
ISNUMBER(
SEARCH(
x,
q
)
)
),
b,
ARRAYTOTEXT(
FILTER(
r,
a
)
),
b
)
)
),
u,
MAP(
p,
LAMBDA(
x,
LET(
a,
ABS(
ISNUMBER(
SEARCH(
TEXTSPLIT(
x,
", "
),
q
)
)
),
b,
BYROW(
a,
LAMBDA(
z,
SUM(
z
)
)
),
d,
ARRAYTOTEXT(
UNIQUE(
FILTER(
r,
b>1
)
)
),
d
)
)
),
v,
IFERROR(
s,
u
),
v
)Excel solution 13 for Match Words in Column Data, proposed by Hamidi Hamid:
=LET(
h,
LAMBDA(
c,
TAKE(
c,
,
1
)
),
g,
LAMBDA(
_p,
IFERROR(
DROP(
REDUCE(
0,
_p,
LAMBDA(
a,
b,
VSTACK(
a,
TEXTSPLIT(
b,
" ",
)
)
)
),
1
),
""
)
),
f,
LAMBDA(
_p,
TAKE(
_p,
,
-1
)
),
x,
g(
A3:A11
),
y,
TOCOL(
IF(
x="",
1/0,
B3:B11&"-"&x
),
3
),
z,
DROP(
REDUCE(
0,
y,
LAMBDA(
a,
b,
VSTACK(
a,
TEXTSPLIT(
b,
"-",
)
)
)
),
1
),
m,
UNIQUE(
z
),
v,
g(
D3:D7
),
u,
IFERROR(
MAP(
v,
LAMBDA(
a,
ARRAYTOTEXT(
FILTER(
h(
m
),
f(
m
)=a
)
)
)
),
""
),
IF(
f(
u
)<>"",
f(
u
),
h(
u
)
)
)Excel solution 14 for Match Words in Column Data, proposed by ferhat CK:
=MAP(D3:D7,
LAMBDA(x,
LET(a,
TEXTSPLIT(
x,
", "
),
b,
FIND(
UPPER(
a
),
UPPER(
A3:A11
),
1
),
ARRAYTOTEXT(FILTER(B3:B11,
ISNUMBER(
TAKE(
b,
,
& 1
)
)*(ISNUMBER(
TAKE(
b,
,
-1
)
)))))))Excel solution 15 for Match Words in Column Data, proposed by Jaroslaw Kujawa:
=BYROW(D3:D7;LAMBDA(xx;LET(aa;DROP(REDUCE("";TEXTSPLIT(xx;;", ");LAMBDA(a;x;LET(y;A3:A11;IF(ISNUMBER(FIND(UPPER(x);UPPER(y)));HSTACK(a;OFFSET(y;0;1));a))));;1);TEXTJOIN(", ";1;TAKE(FILTER(aa;NOT(ISERROR(TAKE(aa;;-1))));;1)))))Excel solution 16 for Match Words in Column Data, proposed by Meganathan Elumalai:
=MAP(
D3:D7,
LAMBDA(
y,
ARRAYTOTEXT(
FILTER(
B3:B11,
BYROW(
DROP(
REDUCE(
"",
TEXTSPLIT(
y,
", "
),
LAMBDA(
a,
v,
HSTACK(
a,
ISNUMBER(
SEARCH(
v,
A3:A11
)
)
)
)
),
,
1
),
LAMBDA(
x,
AND(
x
)
)
)
)
)
)
)Excel solution 17 for Match Words in Column Data, proposed by JvdV –:
=MAP(D3:D7,LAMBDA(s,ARRAYTOTEXT(FILTER(B3:B11,REGEXTEST(A3:A11,REGEXREPLACE(s,"W*(w+)","(?=.*\b$1\b)"),1)))))Excel solution 18 for Match Words in Column Data, proposed by Gerson Pineda:
=MAP(D3:D7,LAMBDA(x,LET(l,B3:B11,f,FILTER,t,BYROW(SEARCH(TEXTSPLIT(x,", "),A3:A11),COUNT),ARRAYTOTEXT(IFERROR(f(l,t>1),f(l,t=1))))))Excel solution 19 for Match Words in Column Data, proposed by Milan Shrimali:
=LET(MAIN,BYROW(A3:B11,LAMBDA(X, LET(A,X,B, SPLIT(CHOOSECOLS(A,1)," ",),TOROW(IFERROR(HSTACK(TOCOL(B),CHOOSECOLS(A,2)),CHOOSECOLS(A,2)))))),MAIN2,WRAPROWS(TOCOL(MAIN),2),FNL,FILTER(MAIN2,CHOOSECOLS(MAIN2,1)<>""),BYROW(D3:D7,LAMBDA(X,ARRAYFORMULA(IF(ISNUMBER(FIND(",",X)),JOIN(",",UNIQUE(BYROW(TOCOL(ARRAYFORMULA(TRIM(SPLIT(X,",")))),LAMBDA(Y,FILTER(CHOOSECOLS(FNL,2),CHOOSECOLS(FNL,1)=Y))))),FILTER(CHOOSECOLS(FNL,2),CHOOSECOLS(FNL,1)=X))))))Excel solution 20 for Match Words in Column Data, proposed by Philippe Brillault:
=LET(cc,
CHOOSECOLS,
FGL,
LAMBDA(z,
LET(c,
TEXTSPLIT(
z,
",",
,
1
),
i,
TOCOL((FIND(
c,
LOWER(
cc(
_T,
1
)
)
)>0)*SEQUENCE(
ROWS(
_T
)
),
3),
TEXTJOIN(",",
,
INDEX(cc(
_T,
2
),
UNIQUE(FILTER(i,
N(BYROW(--(i=TRANSPOSE(
i
)),
SUM)=COLUMNS(
c
)))))))),
BYROW(
D3:D7,
LAMBDA(
z,
FGL(
z
)
)
))
You might be surprised by the formula “BYROW(--(i = TRANSPOSE(
i
)),
SUM)”. I first tried “COUNTIF(
i,
i
)” but it doesn't work inside a LET()Excel solution 21 for Match Words in Column Data, proposed by Miguel Angel Franco García:
=BYROW(D3:D7;
LAMBDA(x;
LET(xx;
x;
a;
TEXTOANTES(
xx;
",";
;
;
;
xx
);
b;
ESPACIOS(
TEXTODESPUES(
xx;
",";
;
;
;
xx
)
);
c;
APILARH(
a;
b
);
d;
HALLAR(
TOMAR(
c;
1
);
A3:A11
);
e;
BYROW(--(ESNUMERO(
d
));
SUMA);
f;
SI(
e>1;
B3:B11;
""
);
UNIRCADENAS(
", ";
VERDADERO;
f
))))Solving the challenge of Match Words in Column Data with Python
Python solution 1 for Match Words in Column Data, proposed by Konrad Gryczan, PhD:
import pandas as pd
import re
path = "599 VLOOKUP.xlsx"
input = pd.read_excel(path, usecols="A:B", skiprows=1, nrows=10)
input2 = pd.read_excel(path, usecols="D", skiprows=1, nrows=6)
test = pd.read_excel(path, usecols="D:E", skiprows=1, nrows=5).sort_values(by="Words").reset_index(drop=True)
input['Column1'] = input['Column1'].str.lower()
input2[['w1', 'w2']] = input2['Words'].str.split(', ', expand=True)
cross_joined = input.assign(key=1).merge(input2.assign(key=1), on='key').drop('key', axis=1)
def check_word_presence(row):
return bool(re.search(r'b' + re.escape(str(row['w1'])) + r'b', row['Column1'])) and
(pd.isna(row['w2']) or bool(re.search(r'b' + re.escape(str(row['w2'])) + r'b', row['Column1'])))
filtered = cross_joined[cross_joined.apply(check_word_presence, axis=1)]
result = filtered.groupby('Words')['Column2'].apply(', '.join).reset_index().sort_values(by='Words')
result.columns = ['Words', 'Result']
print(result.equals(test)) # True
Solving the challenge of Match Words in Column Data with Python in Excel
Python in Excel solution 1 for Match Words in Column Data, proposed by Alejandro Campos:
df_main, search_words_df = xl("A2:B11", headers=True), xl("D2:D7", headers=True)
search_words_df['Results'] = search_words_df['Words'].apply(
lambda q: ', '.join(df_main[df_main['Column1'].str.lower().apply(
lambda x: all(w in x for w in q.lower().split(', ')))]['Column2']))
search_words_df
Python in Excel solution 2 for Match Words in Column Data, proposed by Anshu Bantra:
df_sentences = xl("A2:B11", headers=True)
df_wrds = xl("D2:D7", headers=True)
answer = []
for wrd in df_wrds['Words']:
lst = []
for idx, row in df_sentences.iterrows():
if set(wrd.lower().split(', ')).issubset(set(row['Column1'].lower().split())):
lst.append(row['Column2'])
answer.append(', '.join(lst))
answer
Solving the challenge of Match Words in Column Data with R
R solution 1 for Match Words in Column Data, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/599 VLOOKUP.xlsx"
input = read_excel(path, range = "A2:B11")
input2 = read_excel(path, range = "D2:D7")
test = read_excel(path, range = "D2:E7") %>% arrange(Words)
result = input %>%
mutate(Column1 = str_to_lower(Column1)) %>%
cross_join(input2 %>% separate(Words, c("w1","w2"), sep = ", ", remove = F)) %>%
filter(str_detect(Column1, w1) & (str_detect(Column1, w2) | is.na(w2))) %>%
summarise(Result = str_c(Column2, collapse = ", "), .by = Words) %>%
arrange(Words)
all.equal(result, test)
#> [1] TRUE
&&