LinkBack URL
About LinkBacks
Hi all,
I have a problem, obviously..
I'm searching for a formula which checks if any value from a range of cells (A:A) exists in a long string entered in another cell (B1).
Would be great if someone could help me (without VBA please).
Thanks,
Niels
values from range present in string?
Hi all,
I have a problem, obviously..
I'm searching for a formula which checks if any value from a range of cells (A:A) exists in a long string entered in another cell (B1).
Would be great if someone could help me (without VBA please).
Thanks,
Niels
This is an arrayed formula so may slow down your workbook depending on how long your list is. Do not use A:A if possible use like A2:A200
=IF(MAX(--ISNUMBER(SEARCH($A$1:$A$100,B1)))>0, "Y","N")
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Hi ChemistB,
sorry but it's not working (I did press CSE). It only gives N's, also where it should give Y's.
See for yourself with attached example which, is my aim, should return a Y instead of N.
Thanks,
Niels
The formula you used in C1 is nothing like the formula suggested by Chemist. I tried his formula and it returned Y.
Your formula...
=IF(MAX(--ISNUMBER(LOOKUP(A1:A4,B1)))>0,"Y","N")
His formula...
=IF(MAX(--ISNUMBER(SEARCH($A$1:$A$100,B1)))>0, "Y","N")
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Try this non-array formula:
=IF(SUMPRODUCT(0+ISNUMBER(SEARCH($A$1:$A$7,$B$1)))>0,"Y","N")
Enter
Bonus: Counting:
=SUMPRODUCT(0+ISNUMBER(SEARCH($A$1:$A$7,$B$1)))
Quang PT
As Ford stated you need to use SEARCH, not LOOKUP. Here is your worksheet with the correct formula and some other examples.
Give this formula a try:
=SUM(COUNTIF(B1,"*"&A$1:A$6&"*"))>0
Confirm with Ctrl+Shift+Enter.
Last edited by István Hirsch; 01-23-2015 at 12:34 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks