Formula to find first non-matching number

[PaulW49]

I have 2 columns of numbers and I want to retrieve the first number in Column B, searching from the top cell in the column, that does not match a number in Column A. I'm ok with basic Index, Match, Lookup but this has me stumped. In case above not clear following is an example where 36 is the number required to be retrieved. Any help greatly appreciated.

…..A B
1 10 29
2 29 10
3 18 36 **
4 12 5

 

[Marcelo Branco]

Hi and welcome to Mr Excel forum

Try this

=INDEX($B$1:$B$4,MATCH(TRUE,INDEX(ISNA(MATCH($B$1:$B$4,$A$1:$A$4,0)),0),0))

M.

 

[dermie_72]

in C1 use:
=if(countif(A:A,b1)=0,"NON MATCH","")

Fill down.
Filter C

 

[PaulW49]

Hi Marcelo - thanks for your rapid response and wonderful solution. I wouldn't have come up with that in a million years but now even I can see the logic behind the formula construction. Thanks also to dermie - you guys have made my intro to Mr Excel a very enjoyable experience.

 

[Marcelo Branco]

Hi Marcelo - thanks for your rapid response and wonderful solution. I wouldn't have come up with that in a million years but now even I can see the logic behind the formula construction. Thanks also to dermie - you guys have made my intro to Mr Excel a very enjoyable experience.

Paul,

You are very welcome. Glad for helping

M.