For the common entries in A1 of Sheet3
can you explain how you got the previous "not common result" and how do do the common entries?
I'll do my best.
In the formula bar select the MATCH portion of the formula and hit the F9 function key.
=IFERROR(INDEX(Sheet2!$A$1:$A$8,AGGREGATE(15,6,(ROW($A$1:$A$8)-MIN(ROW($A$1:$A$8))+1)/ISNA(MATCH(Sheet2!$A$1:$A$8,Sheet1!$A$1:$A$6,0)),ROWS(A$1:A1))),"")
You will see an array of {1;2;3;4;5;6;#N/A;#N/A}
The position of the #N/As are the same as the non-matching g h.
Now select
=IFERROR(INDEX(Sheet2!$A$1:$A$8,AGGREGATE(15,6,(ROW($A$1:$A$8)-MIN(ROW($A$1:$A$8))+1)/ISNA(MATCH(Sheet2!$A$1:$A$8,Sheet1!$A$1:$A$6,0)),ROWS(A$1:A1))),"")
and hit the F9 key again. You will see this array.
{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE}
When math operations are applied to TRUE/FALSE it coerces them into their underlying values 1/0. That happens when you divide the row numbers by those TRUE/FALSE.
Now select this portion of the formula and hit the F9.
(ROW($A$1:$A$8)-MIN(ROW($A$1:$A$8))+1)/ISNA(MATCH(Sheet2!$A$1:$A$8,Sheet1!$A$1:$A$6,0))
You will see this array
{#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;7;8}
AGGREGATE has options for ignoring errors. That is what the 6 value is about. AGGREGATE is like the "Swiss Army Knife" of functions. It has 19 to choose from each designated by their function number. SMALL is one of them --- 15.
Small needs what is called a k_value to return the 1st, 2nd, 3rd ... etc smallest number from that array. ROWS(A$1:A1) takes care of that. The remaining row numbers in the above array are then passed to INDEX(Sheet2!$A$1:$A$8, where g and h are. The IFERROR traps any remaining errors and returns "" where they occur.
Using the F9 function key in this manner can be very helpful for analyzing, trouble shooting and self instruction. Though useful examining steps out of context like this can be misleading until you get the hang of it.
Fortunately there is another tool that does the same as above. It is called 'Evaluate formula' (Fx for short). It is in the Formulas ribbon Auditing group. By clicking Evaluate repeatedly Excel shows step by step how the formula is calculated and does each step in context.
I recommend both these devices whenever I can.
The same steps explain the newest formula (common elements). Try these methods to see how you do.
Did this help?
Bookmarks