Brilliant Mike
!
i've no idea how it works, but it does!

Thank you

"Mike H" wrote:

On reflection use this instead, same comment regarding the ranges

=IF(ROWS(B$39:B39)<=COUNTIF($C$39:$C$52,$E$4),INDE X($K$39:$K$52,SMALL(IF($C$39:$C$52=$E$4,ROW($C$39: $C$52)-ROW($C$39)+1),ROWS(B$39:B39))),"")

Mike

"confused" wrote:

Hi Mike,

thanks for this, I managed to get this working on a small simple
spreadsheet, but not in the one I am working on.
The value I am looking up is in E4. The Column this value is in is C39:C1647
and the values I want to look up are in K39:K1647.

This is the formula I have typed in...

=IF(ROWS(C$39:C39)<=COUNTIF($C$39:$C$1647,$E$4),IN DEX($K$39:$K$1647,SMALL(IF($C$39:$C$1647=$E$4,ROW( $C$39:$C$1647)-ROW($E$4)+1),ROWS(C$39:C39))),"")


It is picking up results, but it seems to be random values from the list?
Any thoughts on this??

thank you


"Mike H" wrote:

Hi,

Try this array entered (Ctrl+Shift+Enter)

=IF(ROWS(B$1:B1)<=COUNTIF($A$1:$A$20,$C$1),INDEX($ B$1:$B$20,SMALL(IF($A$1:$A$20=$C$1,ROW($A$1:$A$20)-ROW($C$1)+1),ROWS(B$1:B1))),"")

Search value is in C1 and it works on columns A and B. Drag down to find
second and subsequent matches.

Mike

"confused" wrote:

Hello,

Is there any was to use vlookup to retrieve more than the first entry. My
spreadsheet is as follows...

Product Component
a xx
a xw
a ww
xx 11
xx 12
12 abc
12 def

So each product is made up of numerous components, and these components
themselves are made up of sub components.
If I vlookup product A I can retireve component xx, but not xw or ww . Is
there any way of doing this??

Many thanks,

Willie