Hope most of you have came across this issue. Where you failed to find the solution to find multiple values using Excel Vlookup for Multiple Parameters. Hope you are aware of the Excel Vlookup which we use to find the things in a table or a range by row. Hear the limitation is you will get only the first thing found in the table.What if you have to Multiple Values in the same row matching your search criteria. Do not Worry hear is the solution use the below macro which will help you simplify you query.
Copy the below Macro in Bold into the Macro Tab where you can find in either Developer Tab --> Macro or View Tab --> Macro just give some name and copy it into the sheet.
Option Explicit
Function Multilookup(lookupValue As Variant, lookupRange As Range, resultsRange As Range) As String
Dim s As String 'Results placeholder
Dim sTmp As String 'Cell value placeholder
Dim r As Long 'Row
Dim c As Long 'Column
Const strDelimiter = "|||" 'Makes InStr more robust
s = strDelimiter
For r = 1 To lookupRange.Rows.Count
For c = 1 To lookupRange.Columns.Count
If lookupRange.Cells(r, c).Value = lookupValue Then
'I know it's weird to use offset but it works even if the two ranges
'are of different sizes and it's the same way that SUMIF works
sTmp = resultsRange.Offset(r - 1, c - 1).Cells(1, 1).Value
If InStr(1, s, strDelimiter & sTmp & strDelimiter) = 0 Then
s = s & sTmp & strDelimiter
End If
End If
Next
Next
'Now make it look like CSV
s = Replace(s, strDelimiter, ",")
If Left(s, 1) = "," Then s = Mid(s, 2)
If Right(s, 1) = "," Then s = Left(s, Len(s) - 1)
Multilookup= s 'Return the function
End Function
Screen Shot:
a. Select the Macro Tab.
b. Give the name as shown in the screenshot and click create.
You can use copy and paste special as value function to get rid of the formula and just have values and later separate them using text to columns, separated to comma to get the values in different cells. Below output where I had spread my data into multiple cells.
Copy the below Macro in Bold into the Macro Tab where you can find in either Developer Tab --> Macro or View Tab --> Macro just give some name and copy it into the sheet.
Option Explicit
Function Multilookup(lookupValue As Variant, lookupRange As Range, resultsRange As Range) As String
Dim s As String 'Results placeholder
Dim sTmp As String 'Cell value placeholder
Dim r As Long 'Row
Dim c As Long 'Column
Const strDelimiter = "|||" 'Makes InStr more robust
s = strDelimiter
For r = 1 To lookupRange.Rows.Count
For c = 1 To lookupRange.Columns.Count
If lookupRange.Cells(r, c).Value = lookupValue Then
'I know it's weird to use offset but it works even if the two ranges
'are of different sizes and it's the same way that SUMIF works
sTmp = resultsRange.Offset(r - 1, c - 1).Cells(1, 1).Value
If InStr(1, s, strDelimiter & sTmp & strDelimiter) = 0 Then
s = s & sTmp & strDelimiter
End If
End If
Next
Next
'Now make it look like CSV
s = Replace(s, strDelimiter, ",")
If Left(s, 1) = "," Then s = Mid(s, 2)
If Right(s, 1) = "," Then s = Left(s, Len(s) - 1)
Multilookup= s 'Return the function
End Function
Screen Shot:
a. Select the Macro Tab.
b. Give the name as shown in the screenshot and click create.
c. Copy the macro give above.
4. As shown below where i am finding the lookup value of AAAA & ACDA.
Formula Explained as per the below Output:
1st Value (D1) - Value you wanted to lookup (AAAA)
2nd Value (A1:B11) - Range values where to lookup
3rd Value (B1:B11) - Range of Return values that you need.
Hit Enter.
5. Hear you can see it found the all possible values of AAAA and each value is seperated by Comma - ",".
Please post your comments if you have any question.
