Easy Excel formula to extract values present in two lists

With Dynamic Array formulas we can easily extract values present in two lists. I also cover a formula by Oscar Cronquist for earlier versions of Excel.
Click here to download the Excel file and see step by step written instructions: www.myonlinetr...
Alternatively, you can use Power Query to compare two lists: www.myonlinetr...
Click here for the explanation of the legacy array formula for earlier versions of Excel: www.get-digita...
View my comprehensive courses: www.myonlinetr...
Connect with me on LinkedIn: / myndatreacy

[@AjayAnandXLnCAD]

UNIQUE, SORT, FILTER...Awesome! Thank You 😀

[@wayneedmondson1065]

Nice one Mynda! Thanks for sharing this technique :)) Thumbs up!!

[@MyOnlineTrainingHub]

Thanks for watching, Wayne!

[@SnowPal03]

Hi, Ms, MyOnlineTrainingHub, is there a way to combine index random formula (Ex. =INDEX(Table2[Available Task],RANDBETWEEN(1,4))), with a condition of "sum of number of hours of available task" < 4 hours. Which means, Randomly generate a daily schedule with certain possible task, and the sum numbers of hours must less than 4 hours. Really sorry to putting out this question, cuz i really want to make a daily schedule randomizer but no idea how to do it. Do look forward for a solution from this wonderful teacher.

[@Fiktage]

i guess, Excel formulas will be same as in DAX soon - that will superb update

[@MyOnlineTrainingHub]

Not sure about being the same as DAX because they require a relational database, but there sure are some great new functions now that we have dynamic arrays.

[@hazemali382]

More than Great Mynda ♥

[@MyOnlineTrainingHub]

Thanks, Hazem 😊

[@Deepak_Singh1008]

Really helpful Thanks !!

[@OzduSoleilDATA]

Nice one!

[@sktneer]

You could skip the >0 part in the CountIf formula as a count greater than zero would be considered as True... =SORT(UNIQUE(FILTER(List1,COUNTIF(List2,List1))))

[@MyOnlineTrainingHub]

Too clever! Thanks for sharing :-)

[@sktneer]

@@MyOnlineTrainingHub My pleasure! :)

[@EPMeokazi]

Was there a way to have Excel fetch other files (like Excel files) from specific folders on your computer to add to a data series/table? I thought one of your videos showed that but I can't find it :(

[@MyOnlineTrainingHub]

I cover getting files from a folder with Power Query here: www.myonlinetraininghub.com/power-query-get-files-from-a-folder

[@heikoheimrath7514]

very useful tip 👏

[@kimjay8376]

Hi Thanks a lot for all your videos! I have a question please. I have a master file that shows all product IDs in column A and their expiration date in column B, and I have a list of product IDs where I want to see the expiration date. I'm trying to pull the expiration date of these products using product IDs as a common key. With this formula you showed here, I can confirm whether the product IDs I'm looking for exist in the master file or not but I can't pull the expiration date. I hope my question makes sense. Please help!

[@MyOnlineTrainingHub]

Not sure why you wouldn't be able to use VLOOKUP/XLOOKUP/INDEX & MATCH to bring in the expiration date. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@steveshoulders]

Hi ! Question for everyone ... I am using COUNTIF to generate a BOOLEAN array as the INCLUDE argument in a FILTER function. So, it looks like this : FILTER ( range A, COUNTIF ( range B, criteria ) > 0 ) . COUNTIF needs a range of values, meaning a real "physical" list of cells. COUNTIF cannot use an array as first argument. In my case, range B should be the result of a dynamic array formula, but then considered as a range so COUNTIF can do its job. Since COUNTIF cannot work with an array, I need to first spill it out and use SPILLED RANGE OPERATOR in the COUNTIF formula. Is there a formula so the dynamic array formula is considered as a range ? If this would be possible, then I don't need to spill it first. Hope you understood my problem.

[@MyOnlineTrainingHub]

Hi Sam, it's difficult to visualise, but you could try replacing the COUNTIF with a simple logical test; =FILTER(range, range B = criteria)

[@steveshoulders]

@@MyOnlineTrainingHub i know :-) . thanks for trying anyway. I extended my source data with Power Query - Merge Queries. Finally, preceeded FILTER with UNIQUE. That did the trick ! Looking forward to more video's .

[@JJ_TheGreat]

Could you extend this technique to 3 or more lists? Thanks.

[@MyOnlineTrainingHub]

Only if you're comparing to list 1.

[@renasahmed5477]

I'm using office 365 but I don't have dynamic array functions ,,, anyone has a clue?

[@MyOnlineTrainingHub]

Hi Renas, It's available on the Insider Channel: insider.office.com/

[@GrassHoppah]

Hi, thank you for all your videos, they are awesome. My question is about UNIQUE. I was really excited they finally added it because previously I had to use macros to have that functionality. Maybe it is just me but I can't see it there. Moreover, Microsoft says they plan to make it available in later releases. What version of Excel are you using? Edit: support.office.com/en-us/article/unique-function-c5ab87fd-30a3-4ce9-9d1a-40204fb85e1e Look at the note

[@MyOnlineTrainingHub]

I'm using Office 365 on the Insider channel: insider.office.com/