Google Sheets - Get Matching Values From Two Lists / Ranges Tutorial

[@will-allen]

Simpler formula with exact same result: "=JOIN(", ", filter( ArrayFormula(TRIM(SPLIT(A1, ","))) , ArrayFormula(ISNUMBER(MATCH( ArrayFormula(TRIM(SPLIT(A1, ","))) , ArrayFormula(TRIM(SPLIT(B1, ","))) ,0))) ) )" I took out the unnecessary "transpose" steps. Just one small tweak / cleanup... To the video creator: Awesome!! This is a completely new way for me to use sheets. I'm super stoked!!

[@Aarmaxian]

That was awesome. I am a seasoned excel programmer but I find that google sheets have a leg on excel when it comes to array formulas and they have some additional formulas that Excel does not have.

[@voiceofmarwadi]

I was always scared of Excel or Google Sheet formula. This guy deal with formula so easily, I am fascinated. Love the way you explain. Fanastic work!!👍

[@OmarJuvera]

10:34 an easier solution is to use the function SUBSTITUTE to remove ALL spaces from your string: =TRANSPOSE( SPLIT( SUBSTITUTE(range, " ", "") , "," )) If you want it in a single line format: =TRANSPOSE(SPLIT(SUBSTITUTE(range, " ", ""), ","))

[@prakashbm357]

Can we use name ranges from different sheet rather than list within same sheet?

[@ExcelGoogleSheets]

Yes, you can. Just use it inside IMPORTRANGE function.

[@DanKulibert]

"=ARRAYFORMULA(JOIN(",", FILTER(TRANSPOSE(TRIM(SPLIT(A1,","))), ISNUMBER(SEARCH(TRANSPOSE(TRIM(SPLIT(A1, ","))),B1)))))" By not splitting the second column, you can use 'SEARCH' to see if the item is there. Also, I think you can pull the ArrayFormula to the start of the function. Not sure if it makes it more readable.

[@emilybeauchamp8019]

Wow, your video just saved my life. THANK YOU!

[@carlfogarthy6508]

Great video! Many many thanks. One question: how can we deal with lists where common values are not in the same rows? I suspect that match function wouldn't work longer.... With appreciation for your work, cheers from Italy

[@Dozerson2]

Please, Please do a video using the same setup but that will result in a third list with things that are NOT in both lists. So if they are in only 1 list or the other then the formula will create one list of what is in neither original list. I can't find a video, webpage, or forum that solves this for me. If anyone has a solution let me know.

[@DanKulibert]

You could use the same formula, just replace ISNUMBER with ISERROR. Like "=JOIN(",", FILTER(TRANSPOSE(TRIM(SPLIT(A1,","))), ArrayFormula(ISERROR(MATCH(TRANSPOSE(TRIM(SPLIT(A1, ","))),TRANSPOSE(TRIM(SPLIT(B1,","))),0)))))"

[@darbin5857]

You can use docs.google.com/spreadsheets/d/1aLJzrsC8zVZQnY90aycgWTTAN_mxw7Acsw6wBkwuVQA/copy which will help you compare 2, 3, 4 or practically any number of lists. It will show you exactly which lists every item is in, and display the results in 2 formats.

[@Dozerson2]

@@DanKulibert Thank you so much! 🥳🤯🤯 Long overdue but finally got a chance to use this formula. I don't know you but your answer helped a lot! 😁

[@Dozerson2]

@@darbin5857 Thank you both for the answers!!

[@pratikkumarbathwal6449]

Loved it bro..🤘 ☺️

[@ghayas76]

Thank u for sharing Usefull formula....

[@AltermannEVN]

Beautiful and clean work!

[@tibolefevre]

Can you explain how you do with script ? And how you do to have only the items which doesn't match in 2 lists ? Thanks for your videos !

[@scottneels2628]

Thanks from me too . This would be very helpful for me too if possible please?

[@amandasong3074]

is number only take 1 argument

[@animetech3373]

This is helpful. But i got question for you. Is there way that i can get this kind of result. Let say i have a list of Applicants and each applicants will be assigned to a specific assessor. Now i want to get the list of the applicants assisted by that specific assessor. Is that possible using match function?

[@joshbauer72]

Is it possible to do this with columns too? Like for example: =ARRAYFORMULA(JOIN(",",FILTER(transpose(SPLIT(L:L,",")),ARRAYFORMULA(isnumber(match(transpose(SPLIT(L:L,",")),transpose(SPLIT(M:M,",")),0))))))

[@gabrielt3578]

Thanks! Keep doing Spreadsheets tutorials

[@scotthewett540]

I appreciate the strategy of finding the result you want, and then copying the formula without the equals sign to embed the working formula string into another formula string. I will use this idea.

[@royzemi]

Great videos - I'm still working through. Maybe you've covered this, but is there an easy way to use functions to fill in blank cells in a list so that each item copies down to blank cells below it as follows: Item 1 Blank Blank Item 2 Blank Blank Blank Item 3 Etc etc

[@ExcelGoogleSheets]

=IF(ISBLANK(A1),OFFSET(B1,-1,0),A1)

[@rizwanulkarim1920]

You can use array formula for that also

[@gersonplosconos5409]

Hi buddy... Very great video! But maybe you can help me... I have a google sheet to book appointments... There are 2 columns... 1st column is the date and the 2nd column is a dropdown with slots (morning, afternoon, evening).... My question is: If two different users choose the same date how can they see different options in the dropdown? For example: User A choose january 01 - morning User B choose also january 01 - but only can left the options afternoon and evening (because morning was already taken) Thanks a lot!

[@jozsefolasz8702]

These videos are excellent. They are by far the best. This is how a teacher should teach. Invaluable stuff. Thank you, hope you are well.

[@beef2244]

Could you help me? I would like to input a time in minutes and seconds, and have it display in just seconds for printing. For example if I type in 0:10:06 or 10.6 (as in 10min 6seconds) I want 606 to be displayed in that cell. If yes, could you also do this with hours? Thanks.

[@njlogic4924]

Hi, I want to highlight only overlapping Annual leaves of my employees. Only highlight if two employees are going on annual leaves on the same dates. can this be done?

[@malikastar9265]

That was really well explained, thank you so much for this tutorial, I'm starting to learn from your videos

[@adiliophi]

Great contribution! Thanks

[@arifhaiman5]

Thanks

[@elisaanoardi1547]

HI, your Video is fantastic! but what if I have a bigger tab and i want to select just 3 ranges then, if they are in more rows equals have to been copied in another sheet? for exemple i have: name, name of the teacher and level , than i have others data that do no interest me. I want to create another sheet in wich, all students with the same teacher and the same level will be put toghether, if the teacgher or the level is different d´they shoukld not appear in the next sheet. is it possible? Further I was looking also your video about combinig more cells contents in one, is it possible to do the opposite? to divide teh content of one cell into more cells? Thank you in advance, ißm looking forwards to see your next video!

[@avimehenwal]

Super Awesome. Is it possible to achieve HIrarchies and Levels in sheets?

[@MrJdorrington]

The I've learnt most from your videos is how to "build" formulas. For that I am very appreciative. Thank you.

[@ExcelGoogleSheets]

Great to hear!

[@citizengoodman8023]

👍

[@khemrajrana7322]

Dear sir how to do multiple column name ranged in one time in Google sheet ?

[@briantan920]

What if you wanted to do the opposite thing? Show only the cells that had no duplicates? How would you do that?

[@hakjoonlee9387]

Why don't you use the function "not"?

[@gelanikl8825]

"Hi I work in a small company, we bid cars to customers after paying a guarantee from an auction site in America and after winning the car the customer pays the required value and then we ship the car Topic: I have a table with customer payments and data (date / name / car / car code / receipt / exchange / balance) And the second table in the capital movement of the car that was paid to the site and data (date / name / car / car code / receipt / exchange / balance) I cannot obtain an account statement because we are considered a branch of the main account so I used the tables My question: I want a function or method to read a column in which (the car code) is in the capital movement of the car that was purchased with the column in which (the car code) is in the customer payments column and output the difference between the first and the second table Until I know who the rest is of value and who has not paid Thank you"

[@charlesmurray3255]

What do you mean ?

[@godkeebler]

Impressive explaining

[@kirandeepchoudhry9232]

Awesome explanation

[@sunnyjones1064]

This is awesome brother ❤️❤️❤️❤️❤️❤️❤️❤️❤️❤️❤️

[@LouHazan]

superbe!!! merci beaucoup...

[@benponderin]

Well done. I've been doing this same thing with query() but yours is more elegant and takes up less real estate!

[@agentxx3022]

How do you do it with query ?

[@Mohammedbasim]

HELP ME ON THIS I want to match two column values from a list and find the matching column value. for eg : if the list contains CL1 CL2 CL3 A B 2 A C 3 A D 4 When i give A and B , it should give 2 .

[@ExcelGoogleSheets]

kzbin.info/www/bejne/oprTiHprbrp9qtU

[@KotuKediSerafettinn]

Thanks bro

[@gerenciadoncaballero5323]

elegant!!

[@tibolefevre]

Waou... Amazing !

[@cuneiformscript2665]

🙏🏻

[@lazalazarevic6192]

I love this :)

[@fab99fab]

Awesome !! Can we use numbers as well ?

[@ExcelGoogleSheets]

sure.