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.
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?
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
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 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.
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 ?
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
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.
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'',"") ?
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.
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.
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.
