lookup multiple instances

I have a range say A1:B20. I can find the value of Bx for the First instance where Ax = 'string'.

However I have a case where I want to generate a list of all instances. While i appreciate its not good practice, in my case there will only be 2 or 3 instances, so it wouldn't be too cumbersome to have =[forumala to get instance 1] & ", " & [forumala to get instance 2] & ", " & [forumala to get instance 3].

My problem then is how do i find the second and 3rd instances?

Any pointers appreciated.

Regards

Nathan

Originally Posted by NathanR

I have a range say A1:B20. I can find the value of Bx for the First instance where Ax = 'string'.

However I have a case where I want to generate a list of all instances. While i appreciate its not good practice, in my case there will only be 2 or 3 instances, so it wouldn't be too cumbersome to have =[forumala to get instance 1] & ", " & [forumala to get instance 2] & ", " & [forumala to get instance 3].

My problem then is how do i find the second and 3rd instances?

Any pointers appreciated.

Regards

Nathan

Try this:

=IF(ISERR(SMALL(IF($A$1:$A$20="string",ROW(INDIRECT("1:"&ROWS($A$1:$A$20)))),COLUMNS($A:A))),"",INDEX($B$1:$B$20,SMALL(IF($A$1:$A$20="string",ROW(INDIRECT("1:"&ROWS($A$1:$A$20)))),COLUMNS($A:A))))

ctrl+shift+enter, not just enter
copy across until you see blank

Thanks very much - excellent. Dare I admit i didn't think it was going to be possible, but this works perfectly. It should save hours of cross checking!

Regards

Nathan