VLookup multiple columns and return to a single cell seperated by commas

Re: VLookup multiple columns and return to a single cell seperated by commas

Use this Custom Function;

Function LookupAll(vVal, rTable As Range, ColumnI As Long) As Variant
Dim rFound As Range, lLoop As Long
Dim strResults As String


    With rTable.Columns(1)
    Set rFound = .Cells(1, 1)
        For lLoop = 1 To WorksheetFunction.CountIf(.Cells, vVal)
          Set rFound = .Find(What:=vVal, After:=rFound, LookIn:=xlFormulas, LookAt _
                :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
                False, SearchFormat:=False)
        
            strResults = strResults & "," & rFound(1, ColumnI)
        Next lLoop
    
    End With
    
    
    LookupAll = Trim(Right(strResults, Len(strResults) - 1))
End Function

Use in any cell like;

[frc]=LookupAll(1,$A$1:$C$20,3)[/frc]

Re: VLookup multiple columns and return to a single cell seperated by commas

Sorry about the delay, got busy Try;

Function LookupAll(vVal, vVal2, rTable As Range, ColumnI As Long) As String
Dim rFound As Range, lLoop As Long
Dim strResults As String


    With rTable.Columns(1)
    Set rFound = .Cells(1, 1)
        For lLoop = 1 To WorksheetFunction.CountIf(.Cells, vVal)
          Set rFound = .Find(What:=vVal, After:=rFound, LookIn:=xlFormulas, LookAt _
                :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
                False, SearchFormat:=False)
         If rFound(1, 2) = vVal2 Then
            If InStr(1, strResults, rFound(1, ColumnI)) = 0 Then
                strResults = strResults & "," & rFound(1, ColumnI)
            End If
         End If
        Next lLoop
    End With


    LookupAll = Trim(Right(strResults, Len(strResults) - 1))
End Function

Used like[frc]=LookupAll(1,"A2",$A$1:$C$20,3)[/frc]

Re: VLookup multiple columns and return to a single cell seperated by commas

I was wondering how I would go about changing the function so that it returns 1 value per cell and can be dragged down so each returned value has its own cell rather than putting all values in one cell separated by commas. In addition, I was wondering how I could name the range of cells that gets returned after the value being matched. (as a heads up, I'm using your original code, for single matches, not the second code you posted.)

So I have a table like this:

Dept Code
C2H GL_PROD_XYZ
C2H
C2H GL_DIV_123

The function works perfectly and returns GL_PROD_XYZ and GL_DIV_123 separated by commas but I'd like to have a range that includes GL_PROD_XYZ and GL_DIV_123 and is named "C2H" so that I can later refer to that range for another lookup down the road.

I appreciate any help you might be able to give me.

Re: VLookup multiple columns and return to a single cell seperated by commas

Hi

I'm using your LookupAll function in my code but getting 'Type mismatch' error. I need your help to resolve if I' m using any wrong data. I'm trying to read value from first column of Query4sheet and searching all occurences of it in Column B of another excel (Asc1.xls - range ("B2:C345")) & get the corresponding value from column C in first excel.

Here is my code -
----------------------------

Dim val1 As String


Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("D:\Asc1.xls")
Set objws = objexcel.ActiveWorkbook.Worksheets("Sheet2")


iTscNum = 2
Do while (iTscNum <= 50) ' Trying only for 50 records from QUERY4SHEET
     val1 = Query4sheet.Cells(iTscNum,1).Value 
     Query4sheet.Cells(iTscNum,3) = LookupAll(val1, objws.Range("B2:C345"),2) 
     iTscNum = iTscNum + 1
Loop
End Sub


Function LookupAll(vVal, rTable As Range, ColumnI As Long) As Variant


Dim rFound As Range, lLoop As Long
Dim strResults As String


With rTable.Columns(1)
Set rFound = .Cells(1, 1)


For lLoop = 1 To WorksheetFunction.CountIf(.Cells, vVal) ' ERROR TYPE MISMATCH COMING HERE


Set rFound = .Find(What:=vVal, After:=rFound, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
strResults = strResults & "," & rFound(1, ColumnI)


Next lLoop


End With


LookupAll = Trim(Right(strResults, Len(strResults) - 1))


End Function

----------------------------
Query4Sheet - Read this file
TSC1234
TSC2345

Range contain - Search in this file
TSC2346 CHG11306
TSC1234 CHG11206
TSC2345 CHG11456
TSC1234 CHG11276
TSC1234 CHG11666
TSC2346 CHG11306
TSC1237 CHG11306
TSC1234 CHG11765
TSC2346 CHG12306
TSC2346 CHG16606
TSC2345 CHG13456

Output Required -
TSC1234 CHG11206, CHG11276, CHG11666, CHG11765
TSC2345 CHG11456, CHG12306, CHG13456

Thanks.

Can you plz help?

Re: VLookup multiple columns and return to a single cell seperated by commas

Rajni, please use code tags when posting code, thanks. [noparse]

YOUR CODE HERE

[/noparse]

Re: VLookup multiple columns and return to a single cell seperated by commas

Hi,

I have been trying out your code for lookup all but i am unable to get the result i want.

Assume that i have 3 columns A, B & C

A B C
AU -1 +1
AU -2 +2
AU -3 +5
AU -4 +4
ETC
ETC

I want to return all the positive values to a single cell with the values from column A infront separated by comma when the negative value in B matches with the positive value in C. eg. -1 & +1.

"lookupall" would return AU1, AU2, AU4 all in a single cell.

Please help and thanks in advance.

Re: VLookup multiple columns and return to a single cell seperated by commas

well very good but what should do if i lookup this from 1st sheet and result in 2nd sheet.

thanks

Re: VLookup multiple columns and return to a single cell seperated by commas

Faysalalam,

PLease do not post your questions in threads started by others. Always start a new thread for your questions and, if it helps to clarify your needs, inlcude a link to this (or any other) thread.

When creating your thread be sure to give it a title that accurately describes the thread.