Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vertical lookup with a lookup value that returns multiple matches
Dear Experts:
I would like to perform a vertical lookup with a lookup value that has multiple matches. I know that the VLOOKUP function is designed to return only the corresponding value of the first instance of a lookup value. I would like to get a work-around that identifies multiple matches Example: In cases where the lookup value (Number of Points) occurs several times, I would like to return all the results for this lookup value e.g.: Points Name 2 Smith 1 Gordon 3 John 2 Holten Looking up '2 Points' should result in: Smith Holten The result should list the corresponding values in one cell with manual line breaks (ALT+Enter) I hope this is feasible. Thank you very much in advance for your professional help. Regards, Andreas |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vertical lookup with a lookup value that returns multiple matches
Separate cells is doable with a formula.
-- HTH Bob "andreashermle" wrote in message ... Dear Experts: I would like to perform a vertical lookup with a lookup value that has multiple matches. I know that the VLOOKUP function is designed to return only the corresponding value of the first instance of a lookup value. I would like to get a work-around that identifies multiple matches Example: In cases where the lookup value (Number of Points) occurs several times, I would like to return all the results for this lookup value e.g.: Points Name 2 Smith 1 Gordon 3 John 2 Holten Looking up '2 Points' should result in: Smith Holten The result should list the corresponding values in one cell with manual line breaks (ALT+Enter) I hope this is feasible. Thank you very much in advance for your professional help. Regards, Andreas |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vertical lookup with a lookup value that returns multiple matches
Option Explicit
Sub SAS_filternumbersfornames() Dim mv As Long Dim mc As Long Dim i As Long Dim ms As String mv = 2' numberf to look for mc = 6 'col F For i = 2 To Cells(Rows.Count, mc).End(xlUp).Row If Cells(i, mc) = mv Then ms = ms & Cells(i, mc + 1) & Chr(10) Next i 'MsgBox ms Cells(1, mc + 2) = ms End Sub On Jun 13, 5:02*pm, andreashermle wrote: Dear Experts: I would like to perform a vertical lookup with a lookup value that has multiple matches. I know that the VLOOKUP function is designed to return only the corresponding value of the first instance of a lookup value. I would like to get a work-around that identifies multiple matches Example: In cases where the lookup value (Number of Points) occurs several times, I would like to return all the results for this lookup value e.g.: Points Name 2 * * * * Smith 1 * * * * Gordon 3 * * * * John 2 * * * * Holten Looking up '2 Points' should result in: Smith Holten The result should list the corresponding values in one cell with manual line breaks (ALT+Enter) I hope this is feasible. Thank you very much in advance for your professional help. Regards, Andreas |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vertical lookup with a lookup value that returns multiple matches
On Jun 14, 2:35*pm, Don Guillett Excel MVP
wrote: Option Explicit Sub SAS_filternumbersfornames() Dim mv As Long Dim mc As Long Dim i As Long Dim ms As String mv = 2' numberf to look for mc = 6 'col F For i = 2 To Cells(Rows.Count, mc).End(xlUp).Row If Cells(i, mc) = mv Then ms = ms & Cells(i, mc + 1) & Chr(10) Next i * 'MsgBox ms Cells(1, mc + 2) = ms End Sub On Jun 13, 5:02*pm, andreashermle wrote: Dear Experts: I would like to perform a vertical lookup with a lookup value that has multiple matches. I know that the VLOOKUP function is designed to return only the corresponding value of the first instance of a lookup value. I would like to get a work-around that identifies multiple matches Example: In cases where the lookup value (Number of Points) occurs several times, I would like to return all the results for this lookup value e.g.: Points Name 2 * * * * Smith 1 * * * * Gordon 3 * * * * John 2 * * * * Holten Looking up '2 Points' should result in: Smith Holten The result should list the corresponding values in one cell with manual line breaks (ALT+Enter) I hope this is feasible. Thank you very much in advance for your professional help. Regards, Andreas- Hide quoted text - - Show quoted text - Hi John, Great job. Exactly what I was looking for. Thank you very much for your terrific help. Regards, Andreas |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need a lookup formula that matches 2 values and returns the 3rd va | Excel Discussion (Misc queries) | |||
lookup value if multiple matches are met | Excel Worksheet Functions | |||
Lookup with multiple matches | Excel Discussion (Misc queries) | |||
Multiple matches using LOOKUP | Excel Discussion (Misc queries) | |||
Is it possible to do a vertical lookup that matches on 2 criteria | Excel Worksheet Functions |