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
------------------------------------------------------------------------
JHalsall's Profile: http://www.excelforum.com/member.php...o&userid=34382
View this thread: http://www.excelforum.com/showthread...hreadid=555480

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

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" wrote in
message ...

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

Thank you that has solved my problems.


--
JHalsall
------------------------------------------------------------------------
JHalsall's Profile: http://www.excelforum.com/member.php...o&userid=34382
View this thread: http://www.excelforum.com/showthread...hreadid=555480