vlookup problem

[Cossie]

I would like to return a value from a vlookup based on the value of A2.

In cell C3 I get the correct result, but i would like cell C4 to say browns plains and C5 to say woodridge. The formula is below. Any ideas anyone. Thanks a million. DC

Book4
	A	B	C	D
2	logan
3	Logan	Beenleigh	Beenleigh
4	Logan	BrownsPlains	Beenleigh
5	Logan	Woodridge	Beenleigh
Sheet1

 

[venkat1926]

see this thread

"http://groups.google.com/group/microsoft.public.excel.worksheet.functions/
browse_frm/thread/c41750e2345c7639/ae16ddcb0ec7830f?lnk=st&q=vlookup+all+values+excel&rnum=8#ae16ddcb0ec7830f"

 

[macajm]

I assume you must really have more data than this dummy set,
but why not simply put c3=if(a3=$A$2,b3,"") and copy down in column C.

 

[Aladin Akyurek]

Book5
	A	B	C	D
1
2	logan		3
3	Logan	Beenleigh	Beenleigh
4	Logan	Browns Plains	Browns Plains
5	Logan	Woodridge	Woodridge
6
Sheet1

C2:

=COUNTIF(A3:A5,A2)

C3:

=IF(ROWS($C$3:C3)<=$C$2,INDEX($B$3:$B$5,SMALL(IF($A$3:$A$5=$A$2,ROW($A$3:$A$5)-ROW($A$3)+1),ROWS($C$3:C3))),"")

which is confirmed with control+shift+enter (not just with enter) then coied down.