vlookup problem

Cossie

Active Member
Joined
May 6, 2002
Messages
323
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 :biggrin:
Book4
ABCD
2logan
3LoganBeenleighBeenleigh
4LoganBrownsPlainsBeenleigh
5LoganWoodridgeBeenleigh
Sheet1
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
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

New Member
Joined
Oct 21, 2005
Messages
32
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Book5
ABCD
1
2logan3
3LoganBeenleighBeenleigh
4LoganBrowns PlainsBrowns Plains
5LoganWoodridgeWoodridge
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,090
Messages
5,545,899
Members
410,711
Latest member
Josh324
Top