Matching more than one cell

[JHalsall]

I have two columns of data, the first column contains a reference, and the second contains the data, I want to be able to find data in the second column where the value in the first column matches a given reference. I can do this if the reference is unique in the first column, however the same reference may occur several times in the first column and I want to be abnle to return all values that match not just 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" <JHalsall.2a03kc_1151310902.5448@excelforum-nospam.com> wrote in
message news:JHalsall.2a03kc_1151310902.5448@excelforum-nospam.com...
>
> 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).
>
>
> --
> JHalsall
> ------------------------------------------------------------------------
> JHalsall's Profile:
http://www.excelforum.com/member.php...o&userid=34382
> View this thread: http://www.excelforum.com/showthread...hreadid=555480
>

[JHalsall]

Thank you that has solved my problems.