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]

Check that you don't have any leading/trailing spaces or other hidden characters in d2:d3

no trailing space before or after or other characters

Mary

John

 

[Fluff]

If you type =se into a cell is one of the options SEQUENCE

 

[Perry Cheng]

If you type =se into a cell is one of the options SEQUENCE

nope.

 

[Fluff]

In that case your version does not have any of the recent updates. That was rolled out nearly a year ago at latest.
Try confirming the formula with Ctrl Shift Enter rather than just Enter.

 

[Perry Cheng]

I suspect that because I never had this problem before. This is a company laptop and program. It probably was never updated.

 

[Perry Cheng]

How do you use VLOOKUP in this scenario?

=vlookup(

How about

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

Cell Formulas
Range	Formula
C2:C5	C2	=SUM(COUNTIFS(B2,"*"&$D$2:$D$3&"*"))

How to use VLookup with wild card in this scenario?

=vlookup(B2, "*"&$D$2:$D$3&"*", 1, False) OR

=vlookup("*"&$D$2:$D$3&"*", B2, 1, False)?

 

[Fluff]

Did you try confirming the formula in post#6 with Ctrl Shift Enter?

 

[Perry Cheng]

yes, it does not do anything. I am at home now using a different laptop with Office 365 (Version build 2104). Your formula works.
=SUM(COUNTIFS(B2,"*"&$D$2:$D$3&"*")). If I remove the SUM() part, it spills over the next row. Why?

How do I approach this with VLOOKUP?
would this work? It does not seem to work.
=vlookup(B2, "*"&$D$2:$D$3&"*", 1, False)

 

[Fluff]

Not sure if this will work, but you can try

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

Cell Formulas
Range	Formula
C2:C5	C2	=SUM(COUNTIFS(B2,"*"&$D$2:$D$3&"*"))
E2:E5	E2	=SUM(ISNUMBER(SEARCH($D$2:$D$3,B2))+0)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Perry Cheng]

None of these works. I am very perplexed.