How do I get Vlookup to return multiple matches?

hanoof

New member
Joined
Jun 4, 2013
Messages
2
Reaction score
0
Points
0
How do I get Vlookup to return multiple matches?
i hv
View attachment sample.xlsx
SymbolTimePriceQuantity
GREG.W0006`N
14:59:592.1
1,000
CLPL.W0014`N14:59:593.1902
CLPL.W0014`N14:59:593.11,000
YORK.N0000`N14:59:5617.61,900
DIAL.N0000`N14:59:569.31,000
BIL.N0000`N14:59:563.33,000
MBSL.N0000`N14:59:5519.3500
MBSL.N0000`N14:59:5519.3200
MBSL.N0000`N14:59:5519.3100
MBSL.N0000`N14:59:5519.31,000
I Want lyk this
time price volume
MBSL.N0000`N14:59:5519.3500
14:59:55 PM19.3200
14:59:5519.3100
14:59:55 19.31,000
14:59:5519.310,000
14:59:5519.4500
14:59:5519.42,000
14:59:5519.4100
14:59:5519.41,500
14:59:5519.42,000
pls download nd solve itView attachment sample.xlsx
 

JeffreyWeir

Super Moderator
Staff member
Joined
Mar 22, 2011
Messages
357
Reaction score
0
Points
0
Location
New Zealand
So you essentially want to filter a list, and copy the filtered result somewhere else? If so, you can use the advanced filter for that.

If you have excel 2010, here's an example of how to do it:
1. type the word Symbol in H1
2. in H2 put the particular text string that you want to filter on. For instance, MBSL.N0000`N
3. From the Data tab, select Advanced.
4. Select "Copy to another location"
5.Input your list range into the 'List range' box i.e. $A$1:$E$2183 in your example file
6. For the criteria range, select $H$1:$H$2
7. For the Copy To range, select where you want the files returned to
8. Push Ok

A google search will give you more info. Or check out these links:
http://www.tushar-mehta.com/publish_train/data_analysis/06.shtml
http://dailydoseofexcel.com/archives/2004/11/22/advanced-filtering-in-vba/ (in case you want to automate this)
http://www.excelhero.com/blog/2010/07/excel-partial-match-database-lookup.html has a couple of ways you can do this, with an Advanced Filter (read through the post until you get to the --- UPDATE --- bit that talks about an approach by Sam, and has a link to a workbook here:
http://www.excelhero.com/blog/workbooks/partial_match_lookup_optimized_excelhero.com.xls
 

JeffreyWeir

Super Moderator
Staff member
Joined
Mar 22, 2011
Messages
357
Reaction score
0
Points
0
Location
New Zealand
Hanoof...did this answer your question? It's always nice if you post some feedback when someone has replied to your post.
 
Top