Comparing two data sets and finding matches or non matches

Also in a different column for example: start formula in E3 and drag down

Formula:

Please Login or Register  to view this content.

0 result would be no matches

You did not give any expected answers... and 2700 rows is rather a lot to check through (!!!). Try this array formula:

=IFERROR(INDEX(A:A, SMALL(IF(ISERROR(MATCH($A$3:$A$407&$B$3:$B$407,$C$3:$C$2745&$D$3:$D$2745, 0)), (ROW($A$3:$A$407))), ROWS($F$2:F2))),"")

in F2, copied down. It returns 3 results...

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

I'd structure it bit differently.

Convert each list into Excel Table.

Then load both to PowerQuery/Get & Transform as connection only. Make sure both ID & Status columns are in text data type.

Then create reference to Query Sheet table. Merge DB Extract table into reference created.

Using both ID & Status column, do a Left Anti join.

You should see 3 items from Query Sheet with Status In Progress. This is the mismatch.

Alternately, if you want to see what the source of mismatch is. You can do Left Outer Join and expand the joined table (keeping only status column). This will show you the mismatch on status. You can then add filter flag column with formula.

Please Login or Register  to view this content.

Then filter on the column to show only "FALSE".

See attached sample.

At least we agree on the answer... if not on the method!!

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.