| How do I get Vlookup to return multiple matches? | ||||||
| i hv | View attachment sample.xlsx | |||||
| Symbol | Time | Price | Quantity | |||
| GREG.W0006`N | 14:59:59 | 2.1 | 1,000 | |||
| CLPL.W0014`N | 14:59:59 | 3.1 | 902 | |||
| CLPL.W0014`N | 14:59:59 | 3.1 | 1,000 | |||
| YORK.N0000`N | 14:59:56 | 17.6 | 1,900 | |||
| DIAL.N0000`N | 14:59:56 | 9.3 | 1,000 | |||
| BIL.N0000`N | 14:59:56 | 3.3 | 3,000 | |||
| MBSL.N0000`N | 14:59:55 | 19.3 | 500 | |||
| MBSL.N0000`N | 14:59:55 | 19.3 | 200 | |||
| MBSL.N0000`N | 14:59:55 | 19.3 | 100 | |||
| MBSL.N0000`N | 14:59:55 | 19.3 | 1,000 | |||
| I Want lyk this | ||||||
| time price volume | ||||||
| MBSL.N0000`N | 14:59:55 | 19.3 | 500 | |||
| 14:59:55 PM | 19.3 | 200 | ||||
| 14:59:55 | 19.3 | 100 | ||||
| 14:59:55 | 19.3 | 1,000 | ||||
| 14:59:55 | 19.3 | 10,000 | ||||
| 14:59:55 | 19.4 | 500 | ||||
| 14:59:55 | 19.4 | 2,000 | ||||
| 14:59:55 | 19.4 | 100 | ||||
| 14:59:55 | 19.4 | 1,500 | ||||
| 14:59:55 | 19.4 | 2,000 | ||||
| ||||||
How do I get Vlookup to return multiple matches?
- Thread starter hanoof
- Start date
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
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
Hanoof...did this answer your question? It's always nice if you post some feedback when someone has replied to your post.