Return multiple values in one cell that are not found in a reference list

[Excel_Newbie_Guy]

Hi,

I have searched multiple times and could not find a solution to returning multiple values in a single cell that are not a match, meaning, I would like to return multiple values reported from a single column range that are not a match to a reference list/table.

I have pasted in the cells I am trying to solve. Note that I did use a LOOKUP formula, but now know that it can only return one value.

The data is in Column I and the reference list/table is in Column O and the values that don't match are also in Column O.

Any help provide is greatly appreciated.

 

[Mike Szczesny]

Can you use something like this?

=CONCATENATE(TRANSPOSE(IF(ISNA(MATCH($A$1:$A$10,$C$1:$C$5,0)),$A$1:$A$10&" , "))) Before you enter, you need to highlight the transpose(.......all the way to the second last ) of this formula. Hit F9 and remove both { and }. Hit enter. You will see some FALSE results. Then add on the substitute formula. So it will look like =substitute(your formula,"FALSE", "") Then hit enter a a b d c g d h e j f g h i b , c , e , f , i , j <colgroup><col span="2"><col><col></colgroup><tbody> </tbody>

<tbody>
</tbody>

 

[Excel_Newbie_Guy]

Can you use something like this?

=CONCATENATE(TRANSPOSE(IF(ISNA(MATCH($A$1:$A$10,$C$1:$C$5,0)),$A$1:$A$10&" , "))) Before you enter, you need to highlight the transpose(.......all the way to the second last ) of this formula. Hit F9 and remove both { and }. Hit enter. You will see some FALSE results. Then add on the substitute formula. So it will look like =substitute(your formula,"FALSE", "") Then hit enter a a b d c g d h e j f g h i b , c , e , f , i , j <tbody> </tbody>

<tbody>
</tbody>

Thanks Mike,
Unfortunately this is above my head what you are asking me to do... the only thing I can think of is that the data won't be filtered in any way (unsorted - not ascending or anything) and will also include empty cells. So I am not sure if this formula would work in my case.

I did try and insert it into my spreadsheet following your instructions, but I couldn't replicate what you are suggesting, sorry.


Thank,
Guy