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​

 

[Fluff]

In that case, Im afraid I cannot help any further as I'm pretty sure they should both work if array entered.

 

[Perry Cheng]

In that case, Im afraid I cannot help any further as I'm pretty sure they should both work if array entered.

No worry. You know apparently we also have a webversion of 365Excel, and I tried there and it worked perfectly. So, I am concluding it has to do with the version. Ty so much for your time.

 

[Fluff]

Glad to help & thanks for the feedback.
You could also try & get your IT department to allow Office updates.

 

[Perry Cheng]

They are giving me tons of excuses why not. I am tired of fighting them. ty again. you are great.

 

[Fluff]

They are giving me tons of excuses why not. I am tired of fighting them.

I know the feeling.

 

[jasonb75]

Try the suggestions from post 19 but use SUMPRODUCT instead of SUM.

 

[Perry Cheng]

One last question, how do I accomplish the above, if there is a match, then display the matched name alone. i.e.

I would think this should work, but it isn't. =XLOOKUP(B2,"*"&$D$2:$D$3&"*",$D$2:$D$3,"Not found",2)

 

[Fluff]

How about

=IFNA(LOOKUP(2,1/(ISNUMBER(SEARCH($D$2:$D$3,B2))),$D$2:$D$3),"")

 

[Perry Cheng]

How about

=IFNA(LOOKUP(2,1/(ISNUMBER(SEARCH($D$2:$D$3,B2))),$D$2:$D$3),"")

you are brilliant! It works. Just wondering why syntax was I messed up with the xlookup?

 

[Fluff]

If you don't have the Sequence function, you shouldn't have Xlookup.