Beautiful and useful thank you very much. I would have a request if I may, do you know any way to output the found value instead of just "yes" and ""? that would be awesome if would share that with us.
Instead of "Yes" or "" as the return value, is it possible to change the return value to the matching text? For example, if left column contains "Chocolate", then right column will return the value exactly as "Chocolate". I'm working on an inventory list and I'm hoping to automatically list the characteristics of items based on their text description. Any help is greatly appreciated! Lastly, thank you for taking the time to put together this easy-to-follow and concise tutorial!
Try searching "Display matches if the cell contains text from a list" you will want to use a TextJoin function at the beginning. Using his example: =TEXTJOIN(", ", TRUE, IF(COUNTIF(A1 "*"&$D$2:$D$4&"*"), $D$2:$D$4, "")) In text terms: =TEXTJOIN(", ", TRUE, IF(COUNTIF(CellBeingSearched, "*"&Table or Range&"*"), Table or Range, ""))
=IF(OR(COUNTIF(A2,"*"&$D$2:$D$4&"*")),INDEX($D$2:$D$4,MATCH(1,COUNTIF(A2,"*"&$D$2:$D$4&"*"),0)),"") Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.
Thank you for the very good video! A question please: What do I have to do in order to get the line+row of the cell of the List of column D that was found in each cell of column A, instead of the "YES" answer
Hi all, i tried and the result will not work for the 2nd item onwards from the list. Example it only searches for "Pan" and not "KNI" & "Chocolate". What is the issue here? thanks.
Could you maybe help please, I have the following situation: first value (product name) from the list, second value is the rating from the list and third value is the amount. My task: if i choose value 1 and insert rating, i want to calculate: the product with specific rating to result into respective number. If i have product X and rating Y, take the amount Z. Thanks 🙏
Ohh my god, finally a video i needed. Thanks man. Searching for this function for 1 hour now. Is it possible to refer them in the function with names or it s just easier like this? I am working on an automatic monthly budget calculator linked with my bank account.
Thanks Chester, did exactly what I wanted and nicely explained, esp why the formulas on Excel help sites were not working on O365. Really enjoyed the "oops moment" when you didn't lock the cells before coping down - we've all been there! I didn't know the F4 trick so 2 lessons in 1 Again, thanks
Hey this is not working for me. When I press F9, it does show me 1 for the correct match. but it does not display it as the final outcome. What to do ?
This is exactly what I'm looking for but I need one more step accommodated. Rather then just saying "yes" column B, can the formula be adjusted to show which one of the list was found in the cell in column A?
HI! YOU HAVE EXPLAINED IN A VERY INTERESTING MANNER. BUT I HAVE A QUESTION FOR YOU ON XLOOKUP. SUPPOSE YOU HAVE CREATING A TABLE AND IT HAS THREE COLUMNS IST COLUMN CONTAINS SERIAL NUMBER FROM 1 TO 10 SECOND COLUMN CONTAINS NAME IT HAS TEN NAMES BUT TWO OR THREE NAMES ARE REPEATED LIKE JOHN REPEATED FOR THREE TIMES. THIRD COLUMN HAS FATHER NAME. IN THIS COLUMN TEN DIFFERENT FATHER NAMES HAVE BEEN GIVEN NOW, IN NEW CELL SAY F1 VIA DROP DOWN LIST OR MANUAL TYPING OF SERIAL NUMBER WE NEED NAME IN F2 CELL AND FATHER NAME IN F3 CELL. IS IT POSSIBLE USING XLOOKUP? IF YES THEN PLEASE GUIDE. THANKS.
I need this formula to also return the positive match of the text withing the list. How do I do this? Essentially I need to find the string of the text of on the left table and match and draw the the matching text from the table reference. Example: if A2 contains pan against the table reference, then draw matching pan.
Hi chester..thank you for sharing .. I have a little problem .. When I enter all the formulas, the formula only reads the first list which is correct..the others are wrong .. Could you please help me,
I had same question I figured it out don't know if it will work for you BUT go back to this part =(COUNTIF(C2,"*"&E2:E33&"*")) and then multiple by whatever value makes sense from 1 for you for me that number was *20 or *-20
Hi! Im struggling with the following formula I would like to use. Column B has got text. With discriptions and names etc. Column B is in a table called (discription) For this column I use the =IF(COUNTIF(discription,''*abc*'',''A'',"") But I want to add another if to this formula for example =IF(COUNTIF(discription,''*123*'',''B'',"") And 3 to for more IF('s) Im stuck, hope someone could give some advice ✌️🙏
Based on the example in the video you can try this : =IF(OR(COUNTIF(A2,"*"&$D$2:$D$4&"*")),INDEX($D$2:$D$4,MATCH(1,COUNTIF(A2,"*"&$D$2:$D$4&"*"),0)),"") Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021. In your example i don't understand : =IF(COUNTIF(discription,''*abc*'',''A'',"") . Maybe you want something like : =IF(COUNTIF(discription,''*abc*''),''A'',"") ?
Hello! This is brilliant! This is exactly what I am looking for, plus, how can I tell excel instead of writing YES, return the value it contains from the list? let's say instead of writing yes in col 18, I want excel to return the Broiling Pan for me?
Do you want to return "Broiling pan" from cell A18 from the Product column? =IF(OR(COUNTIF(A2,"*"&$D$2:$D$4&"*")),TRIM(RIGHT(A2,LEN(A2)-FIND("-",A2))),"") Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021. Do you want to return "pan" from the List table in range D2:D4? =IF(OR(COUNTIF(A2,"*"&$D$2:$D$4&"*")),INDEX($D$2:$D$4,MATCH(1,COUNTIF(A2,"*"&$D$2:$D$4&"*"),0)),"") Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.
Great video! I'm attempting to automate this in VBA but the list size I use changes. I'd rather just highlight the column that the words im looking for will be in. Do you know if there is a solution for that?
Hi Sam, there will be but I don't have a video that covers that particular scenario. You can contact me via bluepecantraining.com to access services if you are still stuck.
FANTASTIC VIDEO!! I've looked everywhere for an easy way to do this and now I've found it! Thanks so very much! One issue I ran into though... if a term in the List Column matches ANY part of a word in the Product Column... it identifies it. This is a big issue for me as I'm doing Keyword Research and I want to delete the terms that are in my "Negative Keywords" Column (your "List" Column) from the actual Keyword Research Column. For example... one of the words in my Negatives Column is "lack". When I used your method to identify the terms, it brought back a number of terms that didn't match ANY of the terms in my Negative Keyword Column... Those Keywords all contained the word "black", and it flagged them because the term "lack" was in my Negatives column. So basically it's matching literally ANY part of any word (not just complete words)... so because "lack" is part of "black"... it's flagging it. So... I was curious if you know of any further magic that would alleviate this issue? Just curious my friend... I'll make due in the meantime, but if you wouldn't mind helping me with this issue I would be very appreciative! Thanks again!! You're the BEST!!
If you concatenate " " between "*" and your search list, it will mean the word must start with "lack". You can do the same at the end (but that would remove lacks, lacking etc.
Hi! congratulations for the channel, very useful !. I have a question: is there a formula that tells me that the cell of numbers with a central space "10 30 50" is included in the number cell "5 10 30 40 50" and that returns me as a result the cell "5 10 30 40 50 "? can you help me? it's important please.
![[Live] ศึกมหึมันส์มวยไทยสังเวียนเดือด เวทีมวยชั่วคราวสวนสยาม | จันทร์ 30 กันยายน 2567](http://i.ytimg.com/vi/pM1k6f7JXUc/mqdefault.jpg)
This is great. What I really need is to see it say "Pan", "Knife" or "Chocolate" in the B column
I also need the same please help if you found the solution
I was also hoping to see "Pan", "Knife" or "Chocolate"
Beautiful and useful thank you very much. I would have a request if I may, do you know any way to output the found value instead of just "yes" and ""? that would be awesome if would share that with us.
=IF(OR(COUNTIF($E2,"*"&$AE$2:$AE$17&"*")),"yes","no")
=TEXTJOIN(", ", TRUE, IF(COUNTIF($E2, "*"&$AE$2:$AE$17&"*"), $AE$2:$AE$17, ""))
Instead of "Yes" or "" as the return value, is it possible to change the return value to the matching text? For example, if left column contains "Chocolate", then right column will return the value exactly as "Chocolate". I'm working on an inventory list and I'm hoping to automatically list the characteristics of items based on their text description. Any help is greatly appreciated! Lastly, thank you for taking the time to put together this easy-to-follow and concise tutorial!
You will find the answer in this video; th-cam.com/video/4l97D0A2wIw/w-d-xo.html
Try searching "Display matches if the cell contains text from a list" you will want to use a TextJoin function at the beginning.
Using his example: =TEXTJOIN(", ", TRUE, IF(COUNTIF(A1 "*"&$D$2:$D$4&"*"), $D$2:$D$4, ""))
In text terms: =TEXTJOIN(", ", TRUE, IF(COUNTIF(CellBeingSearched, "*"&Table or Range&"*"), Table or Range, ""))
=IF(OR(COUNTIF(A2,"*"&$D$2:$D$4&"*")),INDEX($D$2:$D$4,MATCH(1,COUNTIF(A2,"*"&$D$2:$D$4&"*"),0)),"")
Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.
@@oliverscheurich9202 thanks mate worked like a charm :)
@@oliverscheurich9202 Thanks, save my day!
Thank you for the very good video!
A question please: What do I have to do in order to get the line+row of the cell of the List of column D that was found in each cell of column A, instead of the "YES" answer
At the very end, how to return the same words from that list? So it will looks like extraction of a certain text from the same list it looking at.
Instead of result yes or true, i need to see the result as Pan or Knife. Please help
Hi all, i tried and the result will not work for the 2nd item onwards from the list. Example it only searches for "Pan" and not "KNI" & "Chocolate". What is the issue here? thanks.
This is really useful. This is great. What I really need is to see it say "Pan", "Knife" or "Chocolate" in the B column. If it is yes.
please assist
Were you able to solve this?
=IF(OR(COUNTIF($E2,"*"&$AE$2:$AE$17&"*")),"yes","no")
=TEXTJOIN(", ", TRUE, IF(COUNTIF($E2, "*"&$AE$2:$AE$17&"*"), $AE$2:$AE$17, ""))
Could you maybe help please, I have the following situation: first value (product name) from the list, second value is the rating from the list and third value is the amount. My task: if i choose value 1 and insert rating, i want to calculate: the product with specific rating to result into respective number.
If i have product X and rating Y, take the amount Z.
Thanks 🙏
Ohh my god, finally a video i needed. Thanks man. Searching for this function for 1 hour now. Is it possible to refer them in the function with names or it s just easier like this? I am working on an automatic monthly budget calculator linked with my bank account.
instead of showing yes as the outcome, can you have it returning as pan or chocolate instead?
May I ask if How about countif for a specific "number list"? Do you have any excel formula for this scenario? Thanknyou
I was about to dislike man, but completed the whole video and found that shift and enter stuff! IT DID WORK.....
THANKS, A LOT MAN!
Does this work on Google sheet? I'm doing this exactly and then looking up a value adjacent to list value.
Thanks... Can you advice if instead of True/False or Yes/No, I need the result to reflect from list i.e Pan, kni, or Chocolate
I am not getting correct result..even I'm trying exactly the way it is shown in video..can you help it out ..??
Thanks Chester, did exactly what I wanted and nicely explained, esp why the formulas on Excel help sites were not working on O365. Really enjoyed the "oops moment" when you didn't lock the cells before coping down - we've all been there! I didn't know the F4 trick so 2 lessons in 1
Again, thanks
Thanks for the video. Ive been searching for this info for ages!
Hey this is not working for me. When I press F9, it does show me 1 for the correct match. but it does not display it as the final outcome. What to do ?
Perfect. To the point with just the right cadence. No intro... Just spot on.
Exactly what I was looking for. To the point and works perfectly!
This is exactly what I'm looking for but I need one more step accommodated. Rather then just saying "yes" column B, can the formula be adjusted to show which one of the list was found in the cell in column A?
It worked for me for ctrl shift enter, I have 2016 version. Thanks very much!
Hello sir, is it possible to this in power query?
Amazing. Helped me at my job today with a complex project!
Still don't get why doesn't work withour the "array" method, but did work for me in the end. Thanks! Voting thumbs up!
In order for formula to work in prior versions you need to hit SHITF CRTL Enter
HI! YOU HAVE EXPLAINED IN A VERY INTERESTING MANNER. BUT I HAVE A QUESTION FOR YOU ON XLOOKUP.
SUPPOSE YOU HAVE CREATING A TABLE AND IT HAS THREE COLUMNS
IST COLUMN CONTAINS SERIAL NUMBER FROM 1 TO 10
SECOND COLUMN CONTAINS NAME IT HAS TEN NAMES BUT TWO OR THREE NAMES ARE REPEATED LIKE JOHN REPEATED FOR THREE TIMES.
THIRD COLUMN HAS FATHER NAME. IN THIS COLUMN TEN DIFFERENT FATHER NAMES HAVE BEEN GIVEN
NOW, IN NEW CELL SAY F1 VIA DROP DOWN LIST OR MANUAL TYPING OF SERIAL NUMBER WE NEED NAME IN F2 CELL AND FATHER NAME IN F3 CELL.
IS IT POSSIBLE USING XLOOKUP?
IF YES THEN PLEASE GUIDE. THANKS.
I need this formula to also return the positive match of the text withing the list. How do I do this?
Essentially I need to find the string of the text of on the left table and match and draw the the matching text from the table reference.
Example: if A2 contains pan against the table reference, then draw matching pan.
You probably saved my life tonight haha, I was pulling my hair out over an assignment and you had the answer. Thanks!!!
Chester, handy info! .. I'm wondering if you can show instead of a Boolean the name as per the Index {1,0,0}?
Man the perfect thing am looking for, your the best. However if the rnag or criteria is huge my excel starts hanging.
Excellent tutorial... how would you populate the 'In List' column B, if you had values in column E for each item in the list?
Thank you so so much. This was exactly what I needed to modify for a compare I need to run for our team
Hi chester..thank you for sharing ..
I have a little problem ..
When I enter all the formulas, the formula only reads the first list which is correct..the others are wrong ..
Could you please help me,
What if I need value instead of yes or no??? ✌️
I had same question I figured it out don't know if it will work for you BUT go back to this part
=(COUNTIF(C2,"*"&E2:E33&"*"))
and then multiple by whatever value makes sense from 1 for you for me that number was *20 or *-20
=IF(OR(COUNTIF($E2,"*"&$AE$2:$AE$17&"*")),"yes","no")
=TEXTJOIN(", ", TRUE, IF(COUNTIF($E2, "*"&$AE$2:$AE$17&"*"), $AE$2:$AE$17, ""))
Can i search by time?
How do you get it to reflect the value instead of 1/true?
Hi!
Im struggling with the following formula I would like to use.
Column B has got text. With discriptions and names etc.
Column B is in a table called (discription)
For this column I use the =IF(COUNTIF(discription,''*abc*'',''A'',"")
But I want to add another if to this formula for example =IF(COUNTIF(discription,''*123*'',''B'',"")
And 3 to for more IF('s)
Im stuck, hope someone could give some advice ✌️🙏
Based on the example in the video you can try this :
=IF(OR(COUNTIF(A2,"*"&$D$2:$D$4&"*")),INDEX($D$2:$D$4,MATCH(1,COUNTIF(A2,"*"&$D$2:$D$4&"*"),0)),"")
Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.
In your example i don't understand : =IF(COUNTIF(discription,''*abc*'',''A'',"") .
Maybe you want something like : =IF(COUNTIF(discription,''*abc*''),''A'',"") ?
Hello! This is brilliant! This is exactly what I am looking for, plus, how can I tell excel instead of writing YES, return the value it contains from the list? let's say instead of writing yes in col 18, I want excel to return the Broiling Pan for me?
Do you want to return "Broiling pan" from cell A18 from the Product column?
=IF(OR(COUNTIF(A2,"*"&$D$2:$D$4&"*")),TRIM(RIGHT(A2,LEN(A2)-FIND("-",A2))),"")
Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.
Do you want to return "pan" from the List table in range D2:D4?
=IF(OR(COUNTIF(A2,"*"&$D$2:$D$4&"*")),INDEX($D$2:$D$4,MATCH(1,COUNTIF(A2,"*"&$D$2:$D$4&"*"),0)),"")
Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.
Thanks, that's handy, but how to pick the exact value matching from the list?
Great video! I'm attempting to automate this in VBA but the list size I use changes. I'd rather just highlight the column that the words im looking for will be in. Do you know if there is a solution for that?
Hi Sam, there will be but I don't have a video that covers that particular scenario. You can contact me via bluepecantraining.com to access services if you are still stuck.
Chester Tugwell I found a temporary alternative using formulas based on the word patterns. I’ll check out the site, thanks!
Good one....very helpful....thank you for sharing
Almost what I need! How do I combine this with 'Ends With' a certain list of text?
I worked it out :)
Fantastic and saved the day. Thank you!
Great tutorial thank you so much! 🙏
Subscribed. You're a genius
Hey how can i do a exact match here...???
exactly what i was looking for! Thanks!!
FANTASTIC VIDEO!! I've looked everywhere for an easy way to do this and now I've found it!
Thanks so very much!
One issue I ran into though... if a term in the List Column matches ANY part of a word in the Product Column... it identifies it. This is a big issue for me as I'm doing Keyword Research and I want to delete the terms that are in my "Negative Keywords" Column (your "List" Column) from the actual Keyword Research Column. For example... one of the words in my Negatives Column is "lack". When I used your method to identify the terms, it brought back a number of terms that didn't match ANY of the terms in my Negative Keyword Column... Those Keywords all contained the word "black", and it flagged them because the term "lack" was in my Negatives column. So basically it's matching literally ANY part of any word (not just complete words)... so because "lack" is part of "black"... it's flagging it.
So... I was curious if you know of any further magic that would alleviate this issue? Just curious my friend... I'll make due in the meantime, but if you wouldn't mind helping me with this issue I would be very appreciative!
Thanks again!! You're the BEST!!
If you concatenate " " between "*" and your search list, it will mean the word must start with "lack". You can do the same at the end (but that would remove lacks, lacking etc.
thank you so much, Chester
Thanks a lot. This helped me a lot by simplifying my work!
Great to hear!
you sir just saved my day :)
Thanks! This is exactly what I was looking for.
omg this is totally what i looking for the whole time , thank you sir
No problem!
Very handy! Thank you!
This was brilliant just what i was looking for! Please keep them coming
You are a savior of the day
Very nice 👍
You make my day, thanks mate
Glad to hear it
Genius thank you
thank you ! do more please !
Excellent explanation, thank you.
You are welcome!
Legend, thank you!
You save my day!
Nice ....Thanks
Danke!
Hi! congratulations for the channel, very useful !. I have a question: is there a formula that tells me that the cell of numbers with a central space "10 30 50" is included in the number cell "5 10 30 40 50" and that returns me as a result the cell "5 10 30 40 50 "? can you help me? it's important please.
I’m very glad you broke down each step with the keyboard shortcuts. ✌🏻thank you