What is wrong with my excel 365?

[Perry Cheng]

So, I am trying to use either VLOOKUP or simple Countif and if I have an array of list, it only look up the first on the list? What am I doing wrong?

=COUNTIF(B2,$E$2:$E$3)

Column B	Column C	Column D	Column E
Tom	=COUNTIF(B2,$E$2:$E$3)​		Mary
Mary	0​		John
Jane	0​
Jack	0​
John	0​

 

[Perry Cheng]

I do in the Web version of the 365. Just not the desktop version.

 

[Fluff]

Your xlookup is the wrong way round, you would have to lookup the values in D2:D3 in B2, but it will spill & also give odd results

+Fluff 1.xlsm
	A	B	C	D	E	F
1		B	C	D (List)
2		Tom, Mary, Jane	Mary	Mary	Mary	Not found
3		John, Edith, Karen	John	John	Not found	Mary
4		Mike, Cecil, Jane			Not found	Not found
5		Tom, Mary, John	John		Mary	Mary
Master

Cell Formulas
Range	Formula
C2:C5	C2	=IFNA(LOOKUP(2,1/(ISNUMBER(SEARCH($D$2:$D$3,B2))),$D$2:$D$3),"")
E2:F5	E2	=TRANSPOSE(XLOOKUP("*"&$D$2:$D$3&"*",B2,$D$2:$D$3,"Not found",2))
Dynamic array formulas.

 

[Perry Cheng]

great! Ty sooooo much!!

 

[Fluff]

Glad we could help & thanks for the feedback.