Excel Magic Trick 1110: Compare 2 Lists and Extract Records: Array Formula Method

See how to compare two lists and extract records with an array formula that uses these functions: MATCH, ISNA, ISNUMBER, IF, ROWS, ROW, INDEX, AGGREGATE and a number of array operations.

[excelisfun]

Excel Magic Trick 1110: Compare 2 Lists and Extract Records:
3) Array Formula Method 

[ExceliAdam]

For example - Item from List 2 are in list 1 it seemed to me unnecessary to count the rows in the function AGGRAGATE therefore I tested formula:

gave the correct result only in the order of list 1

[shawntweber]

Just subscribed - great video, this is exactly what I'm looking for. Except I have a two-dimensional array to scan rather than the one-dimensional list as in your video. I tried MOD(AGGREGATE(), 2)+1 to address an additional column, and duplicated that formula for the INDEX([column]), however got blocked with how to increment the AGGREGATE([k]) independently at different rates for rows and columns to grab the appropriate indicies.

[andersbrinchmann]

Hi Mike, great video! I found what I was looking for extracting similair cells. But if I use a tabel it doesn't seems to work out. Looks like the row formula can't figure out the table data - is that right and do You have a workaround? All the best Anders

[nadermounir]

i came across an issue here, if you put Fran in list number 1 and add another Fran to list 2. This way u will have 2 Fran in list 2 and one Fran in list 1. In this case, the formula will not work and it will remove Fran completely. In other words, the correct would be one Fran in list 2 that is not in list 1. This is because the Match function can only doesn't handle duplicates. I am trying to resolve without success. Any idea Mike?

[excelfan]

Hey I love this. Great video. From this you could do a data validation on steroids v2 in honor of Bob Umlas. Making the leap from who isn't in a list to making a list without blanks. Also thank you for the explanation on the row()-row()+1 being robust. PS My local bookshop had a copy of CTRL+SHIFT+ENTER  loving it,

[ljuarez]

I do this all the time with a vlookup from one list to the other and filter to the #NAs. It's much faster and easier to remember. What's the advantage of using this?

[reyesmaryelzabethm]

how do you copy filtered data without including hidden cells?

[nishchinthkumardewar]

Can you do a video to split the cost and consumption based on days in period [from date and to date]. Eg: I get electricty bills across my organization for different periods across the year. I need the day wise cost & consumption.

Input fields would be Acct nbr, location number, vendor name, image name,  from date, to date, cost, consumption

Output expected: Acct nbr, location number, vendor name, image name,  from date, to date, cost, consumption, day of the month, month number, year, cost per day, consumption per day....

Realities: We may have 200 bills per acct. 2000 location numbers, 5000 accounts, periods can be 30 days, 40 days, 34 days, 69 days, 142 days and 345 days etc....

[akhilmohansamantaray]

If i don wanna use Aggregate and use SMALL function...then can i get the same answer??

[cikakurenosferatu]

Excellent video, but I encountered a problem with comparability when List 1 contains data in two columns (Time-formated hh;mm and Name of Match) and List 2 ontains data in two columns (Time-formated hh;mm and Name of Match)...I want to find Matches from the List 1 with different starting time from the List 2 ...

You can download the file with filled table with the results without the formula...
Definitely better for understanding the problem :)