Saturday 24 August 2013

Excel: Need to look up a value in a list(Column) and return multiple non-corresponding values

Excel: Need to look up a value in a list(Column) and return multiple
non-corresponding values

I'm working on Excel 2007.I'm facing a problem: For Example:
It is looking up a value in a list and it return multiple corresponding
values
formula for that:
=IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2))
I don't want to take corresponding value instead of that I want to take
non-corresponding value. How I will do that(using same formula)?
Please guide me.Any build in formula?
Thank you.

No comments:

Post a Comment