Multiple answer for a VLOOKUP. xl 2003

[joe.muckle]

Hi,

Does any one know of any formulas or VB code which will enable me to return more than one answer (not always the case) to a VLOOKUP.

I need this as I need to know which items I am still waiting for.

Thanks in advance,
Joe.

 

[joe.muckle]

I have tried this suggestion that I gained from another site:

Assuming that A2:B10 contains the lookup table, D2 contains the lookup value, and the corresponding values from Column B are to be returned, try the following...

E2:

=COUNTIF(A2:A10,D2)

F2, copied down:

=IF(ROWS(F$2:F2)<=$E$2,INDEX($A$2:$B$10,SMALL(IF($A$2:$A$10=$D$2,ROW($A$2:$A$10)-ROW($A$2)+1),ROWS(F$2:F2)),2),"")

...confirmed with CONTROL+SHIFT+ENTER. Note that the number 2 at the end of the formula determines the column from which to return the corresponding values. In this case, the corresponding values are to be returned from the second column or Column B.

But all this does is give me the #NAME error, I have changed the values to suit my workbook but not really sure what the formula is doing !!!

I am really struggling with this one can any one help ????

Thanks again.
Joe.

 

[joe.muckle]

Bump