Excel formula

[SolveigBor]

Hi!
I have a problem with a formula that gets too long to be accepted by excel.
I want to make a formula like this, only that I want to add more lines, which I'm not allowed to. Is there a way to make this shorter/easier?

=IF(ISNA(MATCH(O4,$Q$5:$Q$27,0)),O4,(IF(ISNA(MATCH(O5,$Q$5:$Q$27,0)),O5,(IF(ISNA(MATCH(O6,$Q$5:$Q$27,0)),O6,(IF(ISNA(MATCH(O7,$Q$5:$Q$27,0)),O7,(IF(ISNA(MATCH(O8,$Q$5:$Q$27,0)),O8,(IF(ISNA(MATCH(O9,$Q$5:$Q$27,0)),O9,(IF(ISNA(MATCH(O10,$Q$5:$Q$27,0)),O10,(IF(ISNA(MATCH(O11,$Q$5:$Q$27,0)),O11,(IF(ISNA(MATCH(O12,$Q$5:$Q$27,0)),O12,(IF(ISNA(MATCH(O13,$Q$5:$Q$27,0)),O13,(IF(ISNA(MATCH(O14,$Q$5:$Q$27,0)),O14,(IF(ISNA(MATCH(O15,$Q$5:$Q$27,0)),O15,(IF(ISNA(MATCH(O16,$Q$5:$Q$27,0)),O16,(IF(ISNA(MATCH(O17,$Q$5:$Q$27,0)),O17,(IF(ISNA(MATCH(O18,$Q$5:$Q$27,0)),O18,(IF(ISNA(MATCH(O19,$Q$5:$Q$27,0)),O19,(IF(ISNA(MATCH(O20,$Q$5:$Q$27,0)),O20,(IF(ISNA(MATCH(O21,$Q$5:$Q$27,0)),O21,(IF(ISNA(MATCH(O22,$Q$5:$Q$27,0)),O22,(IF(ISNA(MATCH(O23,$Q$5:$Q$27,0)),O23,(IF(ISNA(MATCH(O24,$Q$5:$Q$27,0)),O24,(IF(ISNA(MATCH(O25,$Q$5:$Q$27,0)),O25,(IF(ISNA(MATCH(O26,$Q$5:$Q$27,0)),O26,(IF(ISNA(MATCH(O27,$Q$5:$Q$27,0)),O27,(IF(ISNA(MATCH(O28,$Q$5:$Q$27,0)),O28,(IF(ISNA(MATCH(O29,$Q$5:$Q$27,0)),O29,(IF(ISNA(MATCH(O30,$Q$5:$Q$27,0)),O30,(IF(ISNA(MATCH(O31,$Q$5:$Q$27,0)),O31,(IF(ISNA(MATCH(O32,$Q$5:$Q$27,0)),O32,(IF(ISNA(MATCH(O33,$Q$5:$Q$27,0)),O33,(IF(ISNA(MATCH(O34,$Q$5:$Q$27,0)),O34,(IF(ISNA(MATCH(O35,$Q$5:$Q$27,0)),O35,(IF(ISNA(MATCH(O36,$Q$5:$Q$27,0)),O36,(IF(ISNA(MATCH(O37,$Q$5:$Q$27,0)),O37,(IF(ISNA(MATCH(O38,$Q$5:$Q$27,0)),O38,(IF(ISNA(MATCH(O39,$Q$5:$Q$27,0)),O39,(IF(ISNA(MATCH(O40,$Q$5:$Q$27,0)),O40,(IF(ISNA(MATCH(O41,$Q$5:$Q$27,0)),O41,(IF(ISNA(MATCH(O42,$Q$5:$Q$27,0)),O42,(IF(ISNA(MATCH(O43,$Q$5:$Q$27,0)),O43,(IF(ISNA(MATCH(O44,$Q$5:$Q$27,0)),O44,(IF(ISNA(MATCH(O45,$Q$5:$Q$27,0)),O45,(IF(ISNA(MATCH(O46,$Q$5:$Q$27,0)),O46,(IF(ISNA(MATCH(O47,$Q$5:$Q$27,0)),O47,(IF(ISNA(MATCH(O48,$Q$5:$Q$27,0)),O48,(IF(ISNA(MATCH(O49,$Q$5:$Q$27,0)),O49,(IF(ISNA(MATCH(O50,$Q$5:$Q$27,0)),O50,(IF(ISNA(MATCH(O51,$Q$5:$Q$27,0)),O51,(IF(ISNA(MATCH(O52,$Q$5:$Q$27,0)),O52,(IF(ISNA(MATCH(O53,$Q$5:$Q$27,0)),O53,(IF(ISNA(MATCH(O54,$Q$5:$Q$27,0)),O54,(IF(ISNA(MATCH(O55,$Q$5:$Q$27,0)),O55,(IF(ISNA(MATCH(O56,$Q$5:$Q$27,0)),O56,(IF(ISNA(MATCH(O57,$Q$5:$Q$27,0)),O57,(IF(ISNA(MATCH(O58,$Q$5:$Q$27,0)),O58,(IF(ISNA(MATCH(O59,$Q$5:$Q$27,0)),O59,(IF(ISNA(MATCH(O61,$Q$5:$Q$27,0)),O61,(IF(ISNA(MATCH(O62,$Q$5:$Q$27,0)),O62,(IF(ISNA(MATCH(O63,$Q$5:$Q$27,0)),O63,(IF(ISNA(MATCH(O64,$Q$5:$Q$27,0)),O64,(IF(ISNA(MATCH(O65,$Q$5:$Q$27,0)),O65,(IF(ISNA(MATCH(O66,$Q$5:$Q$27,0)),O66,(IF(ISNA(MATCH(O67,$Q$5:$Q$27,0)),O67,O68)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

 

[GlennUK]

How about an array formula ( entered using Ctrl-Shift-Enter instead of Enter ) of:

=INDEX(O4:O75,SMALL(IF(ISNA(MATCH(O4:O75,$Q$5:$Q$27,0)),ROW(O4:O75)-3,9999),1))

... Excel will put curly brackets around the formula after pressing Ctrl-Shift-Enter, to indicate that it's an array formula.

 

[SolveigBor]

Thanks!
That works fine in cell 1, but I want to make another cell under, which originally was like this:
That doesn't work...

=IF(ISNA(MATCH(O5,$Q$5:$Q$28,0)),O5,(IF(ISNA(MATCH(O6,$Q$5:$Q$28,0)),O6,(IF(ISNA(MATCH(O7,$Q$5:$Q$28,0)),O7,(IF(ISNA(MATCH(O8,$Q$5:$Q$28,0)),O8,(IF(ISNA(MATCH(O9,$Q$5:$Q$28,0)),O9,(IF(ISNA(MATCH(O10,$Q$5:$Q$28,0)),O10,(IF(ISNA(MATCH(O11,$Q$5:$Q$28,0)),O11,(IF(ISNA(MATCH(O12,$Q$5:$Q$28,0)),O12,(IF(ISNA(MATCH(O13,$Q$5:$Q$28,0)),O13,(IF(ISNA(MATCH(O14,$Q$5:$Q$28,0)),O14,(IF(ISNA(MATCH(O15,$Q$5:$Q$28,0)),O15,(IF(ISNA(MATCH(O16,$Q$5:$Q$28,0)),O16,(IF(ISNA(MATCH(O17,$Q$5:$Q$28,0)),O17,(IF(ISNA(MATCH(O18,$Q$5:$Q$28,0)),O18,(IF(ISNA(MATCH(O19,$Q$5:$Q$28,0)),O19,(IF(ISNA(MATCH(O21,$Q$5:$Q$28,0)),O21,(IF(ISNA(MATCH(O22,$Q$5:$Q$28,0)),O22,(IF(ISNA(MATCH(O23,$Q$5:$Q$28,0)),O23,(IF(ISNA(MATCH(O24,$Q$5:$Q$28,0)),O24,(IF(ISNA(MATCH(O25,$Q$5:$Q$28,0)),O25,(IF(ISNA(MATCH(O26,$Q$5:$Q$28,0)),O26,(IF(ISNA(MATCH(O27,$Q$5:$Q$28,0)),O27,(IF(ISNA(MATCH(O28,$Q$5:$Q$28,0)),O28,(IF(ISNA(MATCH(O29,$Q$5:$Q$28,0)),O29,(IF(ISNA(MATCH(O30,$Q$5:$Q$28,0)),O30,(IF(ISNA(MATCH(O31,$Q$5:$Q$28,0)),O31,(IF(ISNA(MATCH(O32,$Q$5:$Q$28,0)),O32,(IF(ISNA(MATCH(O33,$Q$5:$Q$28,0)),O33,(IF(ISNA(MATCH(O34,$Q$5:$Q$28,0)),O34,(IF(ISNA(MATCH(O35,$Q$5:$Q$28,0)),O35,(IF(ISNA(MATCH(O36,$Q$5:$Q$28,0)),O36,(IF(ISNA(MATCH(O37,$Q$5:$Q$28,0)),O37,(IF(ISNA(MATCH(O38,$Q$5:$Q$28,0)),O38,(IF(ISNA(MATCH(O39,$Q$5:$Q$28,0)),O39,(IF(ISNA(MATCH(O40,$Q$5:$Q$28,0)),O40,(IF(ISNA(MATCH(O41,$Q$5:$Q$28,0)),O41,(IF(ISNA(MATCH(O42,$Q$5:$Q$28,0)),O42,(IF(ISNA(MATCH(O43,$Q$5:$Q$28,0)),O43,(IF(ISNA(MATCH(O44,$Q$5:$Q$28,0)),O44,(IF(ISNA(MATCH(O45,$Q$5:$Q$28,0)),O45,(IF(ISNA(MATCH(O46,$Q$5:$Q$28,0)),O46,(IF(ISNA(MATCH(O47,$Q$5:$Q$28,0)),O47,(IF(ISNA(MATCH(O48,$Q$5:$Q$28,0)),O48,(IF(ISNA(MATCH(O49,$Q$5:$Q$28,0)),O49,O50)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

 

[GlennUK]

Explain what the next cell is supposed to do? It looks like it's doing exactly the same except ignoring cell O4.

Maybe you should do something like:
=INDEX(O4:O75,SMALL(IF(ISNA(MATCH(O4:O75,$Q$5:$Q$27,0)),ROW(O4:O75)-3,9999),2))
to get the 2nd missing item?

 

[SolveigBor]

Great!!
This one worked

You saved my day!
Thank you!!