Help with a simple formula

[Marvo]

Hi, I feel a bit dumb asking this question, I'm having a blonde moment. Anyway......

I want to check whether a name in cell A1 is in a list of names found in B1:B50. If it is, I'd like a Yes or better still a tick, if not a No or x.

I know this should be real easy but I can't get my round it.

Thanks in anticipation.

 

[RoryA]

=IF(COUNTIF(B1:B50,A1)>0,"Yes","No")

You can substitute the Yes/No with symbols it you prefer.

 

[kevin9999]

With symbols

Book1
	A	B	C
1	name5	name1	✔
2		name2
3		name3
4		name4
5		name5
Sheet1

Cell Formulas
Range	Formula
C1	C1	=IF(COUNTIF(B1:B50,A1)>0,UNICHAR(10004),UNICHAR(10006))

Book1
	A	B	C
1	nameZ	name1	✖
2		name2
3		name3
4		name4
5		name5
Sheet1

Cell Formulas
Range	Formula
C1	C1	=IF(COUNTIF(B1:B50,A1)>0,UNICHAR(10004),UNICHAR(10006))

 

[Marvo]

That's great, thank you so much. I was coming at it from the wrong direction.

 

[Marvo]

Hi Kevin, can I ask a follow up? At the bottom of the column, what formula would I need to count the amount of ticks? I'm guessing its a COUNTIF?

 

[kevin9999]

If I understand you correctly, and there is still just one value to search for (in A1) but that value could appear multiple times in B1:B50, then yes, a Countif would be the way to go.

 

[Marvo]

There are a list of names in Column A, so I'm copying the formula down to see how many of the names are in column B. So at the bottom of column AI want a formula to count the number of ticks in Column A. If I were adding "Yes" it would be easy but I like the idea of the tick, just don't know how to count them. I can't even find a tick in the segoe UI character map.

 

[kevin9999]

I've switched my laptop off for the night so I can't test this, but try counting for UNICHAR(10004) in your Countif formula rather than "Yes".

 

[Marvo]

That did it, thanks Kevin, much appreciated.