Comparing list using INDEX MATCH etc. in an array-formula

The problem is that the formulas don't work if the tabel is in another position than at the
top of the page.
If I move, or construct, the tabel to the right, ie. starting at another column than A, it's
OK, but if I move, or construct, the tabel in a lower row that 1, the formulas don't work.

Please open the attachment which explains....

Saturn

hi there. without changing too much, you probably can use:
=INDEX(A:A,SMALL(IF(ISNA(MATCH($A$1:$A$12,$C$1:$C$9,0)),ROW($1:$12),""),ROWS(A$1:A2)))

but my preference is usually not ranging up the whole columns & use offsetting of rows:
=INDEX(A$2:A$12,SMALL(IF(ISNA(MATCH($A$2:$A$12,$C$2:$C$12,0)),ROW($A$2:$A$12)-ROW($A$2)+1),ROWS(E$2:E2)))

and to get rid of the NUM error, you can always put an IFERROR in front of either the formulas:
=IFERROR(Formula,"")

Hi Saturn

The TRUE part of the IF is the: ROW($1:$13), which is returning a array: {1;2;3;4;5;6;7;8;9;10;11;12;13}
If your data was elsewhere say A20:C30 (Headers in row 19) and you used ROW($20:$30), the array would be: {20;21;22;23;24;25;26;27;28;29;30} and you would get a error!
So you need to change this to, ROW($20:$30)-ROW($19:$19) then the array would be: {1;2;3;4;5;6;7;8;9;10;11}

Hi Saturn

See the attached file.

Originally Posted by Saturn

Your second proposal didn’t work as expected, you will see that in sheet 2, but the first one works well as shown in
sheet3. Thanks a lot.

I only see a sheet 1......

I'd always use the second formula proposed by benishiryo because the syntax is robust enough to allow the ranges to change or rows to be inserted above, without the results changing - generically

=INDEX(Data,SMALL(IF(ISNA(MATCH(Data,Search_List,0)),ROW(Data)-MIN(ROW(Data))+1),ROWS(X$2:X2)))

confirmed with CTRL+SHIFT+ENTER

where Data is the list to check and Search_List the list to check against...and X2 is the first cell where you place the formula

Hi Saturn

Thanks for the feed back.