Find The Smallest Number MISSING from List

[Simonc64]

Hi Guys, i thought Id found the answer to this with the SMALL function, but either i dont understand how it works or I'm doing something wrong.

Theoretically, I have 2 columns of numbers, lets say the full range of numbers possible is 1 to 11 and these are stored in C1:C11

in Column B I have a random series of numbers, lets say numbers 1,2,4,5 contained in B1:B4..........in B5 I need to find the smallest missing number from the range of possible numbers ie 3 (in this example)

Any help much appreciated

 

[steve the fish]

This may require CTRL-SHIFT-ENTER:

=SMALL(IF(ISERROR(MATCH(C1:C11,B1:B4,0)),C1:C11),1)

 

[Simonc64]

Hi Steve thanks for the reply and solution - oddly that returns 0, and there is'nt a zero in the list of possible numbers available (1-11)

 

[steve the fish]

Did you use the CSE?

 

[Simonc64]

I tried that but the formula wouldnt paste into the cell, when i did it - i copied the formula, clicked in cell B5, pressed CSE then nothing at all...

 

[steve the fish]

Id imagine you have a blank cell? Excel will see that as zero. Maybe use:

=SMALL(IF(C1:C11<>"",IF(ISERROR(MATCH(C1:C11,B1:B4,0)),C1:C11)),1)

 

[Simonc64]

That returns a 1 - im understanding the logic of the formula now, but not the result

 

[steve the fish]

So are you using CSE? If your excel version requires it then this formula wont work without it. You copy the formula. Click a cell. Go to the formula bar. Click at the end of the formula. Press CTRL holding control press SHIFT holding both press enter. You will see { } around the formula.

 

[Simonc64]

ok thats what i was doing wrong! - thanks that works really well!! just one last question then around the use of CSE - if i need to copy that formula (lets say on the click of a macro button that copies all formulas in row f1:k1 into f2:k2) how can i ensure that the macro replicates the CSE entry process?

 

[Simonc64]

its OK steve ive fixed that - thanks so much for your help much appreciated !!