Hi There,
does anybody know a simple formula to check (e.g. by means of TRUE/FALSE) whether the green cell contains text which is present in the yellow range (see example enclosed)?
i.o.w. the green cell currnetly reads 'ain', the check should hence return TRUE as the yellow range comprises trAIN..
Kind regards,
Niels
Try this:
=INDEX(C2:C9,MATCH("*"&E3,C2:C9,0),0)<>""
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Hi there, this formula answers the question, however just now I understand I made a mistake in my original post.
I'm actually looking for a formula which would return TRUE in case the green cell would read 'trainstation'.. So the check would be; whether the text from the range is part of the green cell..
Anybody an idea?
Regards Niels
I can't help you with this very different request, sorry, as I think it might need VBA.
Try
=IF(SUMPRODUCT(--(ISNUMBER(SEARCH(E3,C2:C9)))),"TRUE","FALSE")
sorry John,. it doesn't work.. It works for train, but not for trainstation..
Works for me!!!
=IF(SUMPRODUCT(--(ISNUMBER(SEARCH(E3,C2:C9))));"TRUE";"FALSE")
Hi John, I do work in a different language (Dutch) and am translating formulas and text marks (, for ; etc), but somehow it doesn't work for me.. Can you perhaps upload the spreadsheet with the working formula?
Regards Niels
You might use a formula like this
=COUNT(INDEX(SEARCH(C2:C9,E3),))<>0
Don
Please remember to mark your thread 'Solved' when appropriate.
I think it would be better if you uploaded the workbook where it isn't working.Originally Posted by nielsb
Or ...
=SUMPRODUCT(COUNTIF(E3,"*"&C2:C9&"*"))>0
See attached ...
John - my interpretation is that trainstation needs to be in the green cell and train in the list.
Ali,
I don't think so ......
the green cell currently reads 'ain', the check should hence return TRUE as the yellow range comprises trAIN..
John, I see your view, however that doesn't fit with my problem..
Phuocam's formula works perfect however. The prombelm with xlnitwit's formula is that blank cells are also accepted, which is not ok in my situation.
All thanks for helpoing out!
I do! From post #3:
Looks like Phuocam got there.I'm actually looking for a formula which would return TRUE in case the green cell would read 'trainstation'. So the check would be; whether the text from the range is part of the green cell.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
Blank cells where? If any cell in C2:C9 is blank, both formulas return TRUE for any value in E3 as far as I can see. To fix that you might use
=COUNT(INDEX(SEARCH(C2:C9,E3)*(1/LEN(C2:C9)),))<>0
Last edited by xlnitwit; 12-29-2016 at 09:17 AM.
I don't understand ...
with "ain" in the green cell (E3)
=SUMPRODUCT(COUNTIF(E3,"*"&C2:C9&"*"))>0
returns FALSE
???????
E3 = trainstationwith "ain" in the green cell (E3)
The OP shifted the goalposts in post #3.
@Ali,
Doh!!!!
Hehehe!!!
This thread remains unsolved. The OP us still looking for a 'simple' formula solution.
So if I understood it right...
1. Range may contain blank
2. If any cell in range contains string that is part of full string (Ex. "trainstaion"), it should return True
Following formula should work.
=SUMPRODUCT(--(ISNUMBER(SEARCH(C2:C9,E3)))*(C2:C9<>""))>0
See sample workbook (range extended).
=IF(IFERROR(FIND(E5,C5,1),0)>0,"Yes","No")
Last edited by AliGW; 12-30-2016 at 03:43 AM.
What would be the definition of simple? And where did the OP go? I posted a revised formula earlier but there has been no feedback, which makes it hard to proceed.
The OP started two further threads which were closed as duplicates with links back here. Hopefully he'll be back some time today to pick this up. The words 'simple formula' were used in one of those closed posts - he doesn't want VBA.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks