Vlookup formula for more than one result

**DKerr** · 02-01-2007, 05:19 AM

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))

**Carim** · 02-01-2007, 07:00 AM

Hi,

Take a look at a previous post ...
http://www.excelforum.com/showpost.p...98&postcount=5

**Dav** · 02-01-2007, 07:10 AM

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

**DKerr** · 02-01-2007, 08:19 AM

Thanks for both responses. But....

B col C Col

A PETER
B MARK
C LEWIS
D JOHN
E RAPHAEL
F SARAH
G JUNE
H TANIA
B KEVIN
J SUZIE

DAV

Your formula show "MARK" as a result it should show "KEVIN".

CARIM

I cannot download zip files on my work PC.

I've put B into A1, while my formula in A2 gives "MARK", A3 will need to show KEVIN