Vlookup formula for more than one result

I use (A1) as a search box to look up a reference in column B and return the answer (staff name) in from column C. On some occassions the reference can appear in column B more than once.

The below formula appears in A2, and will only show the first match if references appear more than once. I would like cell A3 to contain, a formula to show the second match, and cell A4 to show a third match. Does anyone know a formula which can do this?



=IF($A1=0,"",VLOOKUP(A1,B1:C10,2,FALSE))

Entered as an array shft ctrl enter

=IF(ISERROR(SMALL(IF($B$1:$B$10=$A$1,ROW($B$1:$B$10),""),ROW(A1))),"",OFFSET($C$1,SMALL(IF($B$1:$B$10=$A$1,ROW($B$1:$B$10),""),ROW(A1))-1,0))

regards

Dav

No need, I guess I can download it at home. Thanks

I gave you a response for the first answer so for the mark, if you copy it down to the rows below it will give kevin and then blanks. The formual was to replace the vlookup for the first match as well


Regards

Dav

My mistake, works perfectly, thanks.