| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup question - bring in all values?
I believe vlookup is set to bring in the first match in the range. But is it
possible to bring in all matches and display them in a list separated by commas? So: A Yellow B Red C Green B Blue D Purple B Orange E Black Input into D1 =+vlookup("B",A1:B7,2,FALSE) will retrieve "Red." Is there a formula to use that will bring in (Red, Blue, Orange) - all into D1?? Thanks |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup question - bring in all values?
Hi,
You could but here's an alternative array formula See below on how to enter =INDEX($B$1:$B$7,SMALL(IF($A$1:$A$7=$E$1,ROW($A$1: $A$7)-ROW($E$1)+1),ROWS(B$1:B1))) This returns the first match and if you drag down 1 row it returns the second. You could then concatenate the value returned into a single cell and hide these formula =F1&","&F2&","&F3 I used F1 - f3 for the array formula If you want to do it in a single cell then it all gets a bit long winded but here's a way =INDEX($B$1:$B$7,SMALL(IF($A$1:$A$7=$E$1,ROW($A$1: $A$7)-ROW($E$1)+1),ROWS(B$1:B1)))&","&INDEX($B$1:$B$7,SM ALL(IF($A$1:$A$7=$E$1,ROW($A$1:$A$7)-ROW($E$1)+1),ROWS(B$1:B2)))&","&INDEX($B$1:$B$7,SM ALL(IF($A$1:$A$7=$E$1,ROW($A$1:$A$7)-ROW($E$1)+1),ROWS(B$1:B3))) This cater for 3 matches but throws an error for less than 3. This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. "Kevin W" wrote: I believe vlookup is set to bring in the first match in the range. But is it possible to bring in all matches and display them in a list separated by commas? So: A Yellow B Red C Green B Blue D Purple B Orange E Black Input into D1 =+vlookup("B",A1:B7,2,FALSE) will retrieve "Red." Is there a formula to use that will bring in (Red, Blue, Orange) - all into D1?? Thanks |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup question - bring in all values?
Forgot to mention in the formula E1 is the lookup value
"Mike H" wrote: Hi, You could but here's an alternative array formula See below on how to enter =INDEX($B$1:$B$7,SMALL(IF($A$1:$A$7=$E$1,ROW($A$1: $A$7)-ROW($E$1)+1),ROWS(B$1:B1))) This returns the first match and if you drag down 1 row it returns the second. You could then concatenate the value returned into a single cell and hide these formula =F1&","&F2&","&F3 I used F1 - f3 for the array formula If you want to do it in a single cell then it all gets a bit long winded but here's a way =INDEX($B$1:$B$7,SMALL(IF($A$1:$A$7=$E$1,ROW($A$1: $A$7)-ROW($E$1)+1),ROWS(B$1:B1)))&","&INDEX($B$1:$B$7,SM ALL(IF($A$1:$A$7=$E$1,ROW($A$1:$A$7)-ROW($E$1)+1),ROWS(B$1:B2)))&","&INDEX($B$1:$B$7,SM ALL(IF($A$1:$A$7=$E$1,ROW($A$1:$A$7)-ROW($E$1)+1),ROWS(B$1:B3))) This cater for 3 matches but throws an error for less than 3. This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. "Kevin W" wrote: I believe vlookup is set to bring in the first match in the range. But is it possible to bring in all matches and display them in a list separated by commas? So: A Yellow B Red C Green B Blue D Purple B Orange E Black Input into D1 =+vlookup("B",A1:B7,2,FALSE) will retrieve "Red." Is there a formula to use that will bring in (Red, Blue, Orange) - all into D1?? Thanks |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup question - bring in all values?
Mike - I'm sorry I can not get this to work; if looking at the example below
A Yellow B Red C Green B Blue D Purple B Orange E Black And put B in cell E1; it kicks back a #VALUE! I'm using the same scenario - however I just need it to kick back Blue and Blue only or the value in cell 'B4' in the example above. Can you please explain better? Sorry for the confusion... thank you! "Mike H" wrote: Forgot to mention in the formula E1 is the lookup value "Mike H" wrote: Hi, You could but here's an alternative array formula See below on how to enter =INDEX($B$1:$B$7,SMALL(IF($A$1:$A$7=$E$1,ROW($A$1: $A$7)-ROW($E$1)+1),ROWS(B$1:B1))) This returns the first match and if you drag down 1 row it returns the second. You could then concatenate the value returned into a single cell and hide these formula =F1&","&F2&","&F3 I used F1 - f3 for the array formula If you want to do it in a single cell then it all gets a bit long winded but here's a way =INDEX($B$1:$B$7,SMALL(IF($A$1:$A$7=$E$1,ROW($A$1: $A$7)-ROW($E$1)+1),ROWS(B$1:B1)))&","&INDEX($B$1:$B$7,SM ALL(IF($A$1:$A$7=$E$1,ROW($A$1:$A$7)-ROW($E$1)+1),ROWS(B$1:B2)))&","&INDEX($B$1:$B$7,SM ALL(IF($A$1:$A$7=$E$1,ROW($A$1:$A$7)-ROW($E$1)+1),ROWS(B$1:B3))) This cater for 3 matches but throws an error for less than 3. This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. "Kevin W" wrote: I believe vlookup is set to bring in the first match in the range. But is it possible to bring in all matches and display them in a list separated by commas? So: A Yellow B Red C Green B Blue D Purple B Orange E Black Input into D1 =+vlookup("B",A1:B7,2,FALSE) will retrieve "Red." Is there a formula to use that will bring in (Red, Blue, Orange) - all into D1?? Thanks |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup question - bring in all values?
I appreciate the help. What I'm trying to do is have a summary on another
tab, so I'd have A B C D going down the rows. So I'd like to drag the formula down, but I don't want to have to insert rows between the values of B & C (so only one row per letter). Is there a way I can copy the formula across columns? Then I can concatenate each column? Your formula brought in the first value for me, as you said, but retrieved the error #NUM! for the second value when I dragged it down one cell. "Mike H" wrote: Forgot to mention in the formula E1 is the lookup value "Mike H" wrote: Hi, You could but here's an alternative array formula See below on how to enter =INDEX($B$1:$B$7,SMALL(IF($A$1:$A$7=$E$1,ROW($A$1: $A$7)-ROW($E$1)+1),ROWS(B$1:B1))) This returns the first match and if you drag down 1 row it returns the second. You could then concatenate the value returned into a single cell and hide these formula =F1&","&F2&","&F3 I used F1 - f3 for the array formula If you want to do it in a single cell then it all gets a bit long winded but here's a way =INDEX($B$1:$B$7,SMALL(IF($A$1:$A$7=$E$1,ROW($A$1: $A$7)-ROW($E$1)+1),ROWS(B$1:B1)))&","&INDEX($B$1:$B$7,SM ALL(IF($A$1:$A$7=$E$1,ROW($A$1:$A$7)-ROW($E$1)+1),ROWS(B$1:B2)))&","&INDEX($B$1:$B$7,SM ALL(IF($A$1:$A$7=$E$1,ROW($A$1:$A$7)-ROW($E$1)+1),ROWS(B$1:B3))) This cater for 3 matches but throws an error for less than 3. This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. "Kevin W" wrote: I believe vlookup is set to bring in the first match in the range. But is it possible to bring in all matches and display them in a list separated by commas? So: A Yellow B Red C Green B Blue D Purple B Orange E Black Input into D1 =+vlookup("B",A1:B7,2,FALSE) will retrieve "Red." Is there a formula to use that will bring in (Red, Blue, Orange) - all into D1?? Thanks |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup question - bring in all values?
Try this UDF (User Defined function). From workbook launch VBE using Alt+F11.
From menu Insert a Module and paste the below function.Close and get back to workbook and try the below formula. With your sample data try the below formula =VLOOKUP_CONCAT(A1:B7,"B",2,", ") Syntax: =VLOOKUP_CONCAT(rngRange,strLookupValue,intColumn, strDelimiter) rngRange is the Range strLookupValue is the lookup string or cell reference inColumn is the column to be concatenated strDelimiter Optional . Default is space Examples: '1. To vlookup 'jacob' and concatenate all entries of 2nd column =VLOOKUP_CONCAT(A1:B10,"jacob",2) '2. with lookup value in cell C1 =VLOOKUP_CONCAT(A1:B10,C1,2) '3. with delimiter as comma =VLOOKUP_CONCAT(A1:B10,C1,2,",") Function VLOOKUP_CONCAT(rngRange As Range, _ strLookupValue As String, intColumn As Integer, _ Optional strDelimiter As String = " ") Dim lngRow As Long For lngRow = 1 To rngRange.Rows.Count If StrComp(CStr(rngRange(lngRow, 1)), _ strLookupValue, vbTextCompare) = 0 Then _ VLOOKUP_CONCAT = VLOOKUP_CONCAT & strDelimiter & _ rngRange(lngRow, intColumn) Next VLOOKUP_CONCAT = Mid(VLOOKUP_CONCAT, Len(strDelimiter) + 1) End Function If this post helps click Yes --------------- Jacob Skaria "Kevin W" wrote: I believe vlookup is set to bring in the first match in the range. But is it possible to bring in all matches and display them in a list separated by commas? So: A Yellow B Red C Green B Blue D Purple B Orange E Black Input into D1 =+vlookup("B",A1:B7,2,FALSE) will retrieve "Red." Is there a formula to use that will bring in (Red, Blue, Orange) - all into D1?? Thanks |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup question - bring in all values?
A little slow for my 2000 rows of data but works perfectly! Thanks!
"Jacob Skaria" wrote: Try this UDF (User Defined function). From workbook launch VBE using Alt+F11. From menu Insert a Module and paste the below function.Close and get back to workbook and try the below formula. With your sample data try the below formula =VLOOKUP_CONCAT(A1:B7,"B",2,", ") Syntax: =VLOOKUP_CONCAT(rngRange,strLookupValue,intColumn, strDelimiter) rngRange is the Range strLookupValue is the lookup string or cell reference inColumn is the column to be concatenated strDelimiter Optional . Default is space Examples: '1. To vlookup 'jacob' and concatenate all entries of 2nd column =VLOOKUP_CONCAT(A1:B10,"jacob",2) '2. with lookup value in cell C1 =VLOOKUP_CONCAT(A1:B10,C1,2) '3. with delimiter as comma =VLOOKUP_CONCAT(A1:B10,C1,2,",") Function VLOOKUP_CONCAT(rngRange As Range, _ strLookupValue As String, intColumn As Integer, _ Optional strDelimiter As String = " ") Dim lngRow As Long For lngRow = 1 To rngRange.Rows.Count If StrComp(CStr(rngRange(lngRow, 1)), _ strLookupValue, vbTextCompare) = 0 Then _ VLOOKUP_CONCAT = VLOOKUP_CONCAT & strDelimiter & _ rngRange(lngRow, intColumn) Next VLOOKUP_CONCAT = Mid(VLOOKUP_CONCAT, Len(strDelimiter) + 1) End Function If this post helps click Yes --------------- Jacob Skaria "Kevin W" wrote: I believe vlookup is set to bring in the first match in the range. But is it possible to bring in all matches and display them in a list separated by commas? So: A Yellow B Red C Green B Blue D Purple B Orange E Black Input into D1 =+vlookup("B",A1:B7,2,FALSE) will retrieve "Red." Is there a formula to use that will bring in (Red, Blue, Orange) - all into D1?? Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Bring values in a column into a row | Excel Worksheet Functions | |||
| Vlookup, Bring up certain Info. only??? | Excel Discussion (Misc queries) | |||
| Vlookup, Can I only have it bring up a transactions once? | Excel Discussion (Misc queries) | |||
| Can you use a vlookup to bring in a picture? | Excel Discussion (Misc queries) | |||
| How can I bring forward formats with Vlookup? | Excel Worksheet Functions | |||
![Kevin W[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
