Help with a simple formula

Marvo

Board Regular
Joined
Nov 27, 2023
Messages
172
Office Version
  1. 2021
Platform
  1. Windows
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.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Excel Formula:
=IF(COUNTIF(B1:B50,A1)>0,"Yes","No")

You can substitute the Yes/No with symbols it you prefer.
 
Upvote 0
Solution
With symbols
Book1
ABC
1name5name1
2name2
3name3
4name4
5name5
Sheet1
Cell Formulas
RangeFormula
C1C1=IF(COUNTIF(B1:B50,A1)>0,UNICHAR(10004),UNICHAR(10006))


Book1
ABC
1nameZname1
2name2
3name3
4name4
5name5
Sheet1
Cell Formulas
RangeFormula
C1C1=IF(COUNTIF(B1:B50,A1)>0,UNICHAR(10004),UNICHAR(10006))
 
Upvote 0
That's great, thank you so much. I was coming at it from the wrong direction.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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".
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top