Matching more than one cell

[JHalsall]

I have two columns of data, the first column contains a reference, an
the second contains the data, I want to be able to find data in th
second column where the value in the first column matches a give
reference. I can do this if the reference is unique in the firs
column, however the same reference may occur several times in the firs
column and I want to be abnle to return all values that match not jus
the first one.

Thank you in advance for your assistance in this matter

 

[JHalsall]

I had an error in my formula have now sorted it.

My formula looks like this:

=INDEX($B:$B,SMALL(IF($A$2:$A$1000=E2,ROW($A$2:$A$1000 )),ROW(A1)))

comments on if there is a better way to do this.

Also I now want to be able to see if any of these values contain a
certain value, I am using the below formula which at the moment always
returns F2,

=IF(F$2:F$10="FALSE","FALSE",F2).

 

[Bob Phillips]

You could trap the error

=IF(ISERROR(SMALL(IF($A$2:$A$1000=$E$2,ROW($A$2:$A$1000 )),ROW(A1))),"",
INDEX($B:$B,SMALL(IF($A$2:$A$1000=$E$2,ROW($A$2:$A$1000 )),ROW(A1))))

=IF(COUNTIF(F$2:F$10,"FALSE")>0,"FALSE",F2)

perhaps


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[JHalsall]

Thank you that has solved my problems.