LinkBack URL
About LinkBacks
Hi, need a formula to find a value from another cell. Please see file attached. I need to find if a value in column B and C is in column A and put the output in column D. The output can be be Yes or Np.
Thanks!
Hi, need a formula to find a value from another cell. Please see file attached. I need to find if a value in column B and C is in column A and put the output in column D. The output can be be Yes or Np.
Thanks!
Try this:
=IFERROR(IF(LOOKUP(1000,SEARCH(B2,A2),"")&LOOKUP(1000,SEARCH(C2,A2),"")="","Yes"),"No")
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.
Try
=IFERROR(IF(AND(SEARCH(B3,A3),SEARCH(C3,A3)),"Yes"),"No")
Thank you both but it doesn't give accurate results. Please see attached.
You have the formulae wrongly associated with names! What result do you expect for row 2 as RED doesn't exist anywhere in the data?
This
=IFERROR(IF(SEARCH(B3,A3),"yes"),IFERROR(IF(SEARCH(C3,A3),"Yes"),"No"))
Thanks. I will be make it this way, please see new sheet. Formula needs to find any value in A1 for example in all column B.
I think you will need to parse the data in A into separate words and then use SEARCH to find any matches.
I can't, in most cases column A have more than 10 words. I think it should be a simple formula. Thank you.
It's column B that will need separating out.
Please don't tell your highly experienced helpers that it should be a simple formula when you are here asking how to do it: clearly, you are not the one to judge the complexity of the problem, otherwise you would not need to ask for help.
Maybe try
=IF(SUMPRODUCT(COUNTIFS(B$2:B$8,"*"&FILTERXML("<x><m>"&SUBSTITUTE(A2," ","</m><m>")&"</m></x>","//m")&"*")),"Yes","No")
I don't think this is a simple formula.
ARRAY formula
=IFERROR(IF(SUM(1*(ISNUMBER(SEARCH(B2,$A$2:$A$3))),1*(ISNUMBER(SEARCH(C2,$A$2:$A$3)))),"Yes","No"),"")
To enter ARRAY formula
Copy and paste the formula in cell
Press F2
Press Ctrl+Shift+Enter together
Excel covers the formula with {}.
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
Sorry, there was no bad intention, it is clear im not the one who knows excel here.Originally Posted by AliGW
Thank you. A bit better, please see attached. I checked few results and some were wrong. Can you check? Maybe we need to make some tuning to the formula.
Does this work for you?
=IFERROR(IF(SUM(1*(ISNUMBER(SEARCH(B2,$A$2:$A$247)))),"Yes","No"),"")
Form what I interpret.
Only 1 wrong is A13 "T" is every what in Column B
There are "Dog" in A12, A226, B37
There are "Pet" in A66, A73 and B44, B46, B69, B78
=IF(COUNT(SEARCH(TRANSPOSE(" "&FILTERXML("<x><m>"&SUBSTITUTE(A2," ","</m><m>")&"</m></x>","//m")&" ")," "&$B$2:$B$91&" ")),"Yes","No")
Unless you want case sensitive, change Search to Find
=IF(COUNT(Find(TRANSPOSE(" "&FILTERXML("<x><m>"&SUBSTITUTE(A2," ","</m><m>")&"</m></x>","//m")&" ")," "&$B$2:$B$91&" ")),"Yes","No")
These formula need to confirm with Ctrl+Shift+Enter
Results in column G: Search matching words in column A with those in B
Please Login or Register to view this content.
Will try, thanks.
I pasted the results of Bo_Ry formulae into the w/book and compared the results to the UDF and only found 2 differences between column D formula and UDF.
The common factor in both cases is there is "&" in the text. I don't know if that has any bearing on the formulae used, and as I don't have a current version of Excel, I can't test this "hypothesis".
See attached (marked in yellow).
Last edited by JohnTopley; 04-11-2021 at 06:43 AM.
FilterXML doesn't like &
use this
=IF(COUNT(SEARCH(TRANSPOSE(" "&FILTERXML("<x><m>"&SUBSTITUTE(SUBSTITUTE(A2,"&","&")," ","</m><m>")&"</m></x>","//m")&" ")," "&$B$2:$B$91&" ")),"Yes","No")
for Excel 2010 where FILTERXML is not available try this
=IF(COUNT(INDEX(SEARCH(" "&TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",99)),COLUMN(A$1:INDEX(A$1:I$1,LEN(A2)-LEN(SUBSTITUTE(A2," ",))+1))*99-98,99))&" "," "&$B$2:$B$91&" "),)),"Yes","No")
@Bo_Ry
It appears my hypothesis is correct! Anyway, you have resolved it.
Thank you for the 2010 formula - if I can get my head round it!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks