Help with formula

[Chris_t_2k5]

Hi I have a formula that I am using and it works I just need to know what all
of the bits f it mean and HOW it works.

=SMALL(IF(ISERROR(MATCH(ROW(INDIRECT("1:15")),A1:C1,0)),ROW(INDIRECT("1:15"))),1)

it is an array formula.

Thanks in advance

[daddylonglegs]

Hi Chris

This bit

ROW(INDIRECT("1:15"))

generates this array

{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15}

then MATCH(ROW(INDIRECT("1:15")),A1:C1,0)

generates another array based on whether 1 to 15 appears in the range A1:C1 - if it does you get the position, if not #N/A so if A1=6, B1=2, C1=11 you get

{#N/A;2;#N/A;#N/A;;#N/A;1;#N/A;#N/A;#N/A;#N/A;3;#N/A;#N/A;#N/A;#N/A}

ISERROR converts this to

{TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE}

[although you only really require ISNA]

Now the IF part looks like

IF({TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE},ROW(INDIRECT("1:15")))

which becomes

{1;FALSE;3;4;5;FALSE;7;8;9;10;FALSE;12;13;14;15}

Now the whole formula is reduced to

=SMALL({1;FALSE;3;4;5;FALSE;7;8;9;10;FALSE;12;13;14;15},1)

so that returns the smallest value from that range, i.e. 1

In short it should return the smallest value 1 to 15 which doesn't appear in A1:C1.

This specific formula can never return a value greater than 4 so some parts of it are superfluous, perhaps you use other versions with a larger range or using =SMALL(range,2) or =SMALL(range,3)?

If you only ever use this specific formula I think you could simplify it to

=MATCH(TRUE,ISNA(MATCH({1;2;3;4},A1:C1,0)),0)