How to lookup multiple values based on multiple criteria in one cell?

[excelvbanoob420]

Hello,

I need help for you guys for the following problem. I want to lookup multiple values based on multiple criteria in one cell. Below is the sample input and expected output:

Input Table 1:

IINPUTCOLUMN

FJ-SJ-0000-0101 FJ-RJ-0000

FJ-SJ-0000-0112 FJ-SJ-0000-0113 FJ-SJ-0000-0114

FJ-RJ-0001 FJ-SJ-0000-0115 FJ-RJ-0002 FJ-SJ-0000-0116 FJ-SJ-0000-0117

Input Table 2 (Lookup Table):

LookupTable Column1	LookupTable Column2
FJ-SJ-0000-0101	Valid P/N
FJ-SJ-0000-0112	Valid P/N
FJ-SJ-0000-0113	InValid P/N
FJ-SJ-0000-0114	Valid P/N
FJ-SJ-0000-0116	InValid P/N

OUTPUT (The lookup output expected is in OUTPUT COLUMN):

IINPUTCOLUMN	OUTPUTCOLUMN
FJ-SJ-0000-0101 FJ-RJ-0000	Valid P/N N/A
FJ-SJ-0000-0112 FJ-SJ-0000-0113 FJ-SJ-0000-0114	Valid P/N InValid P/N Valid P/N
FJ-RJ-0001 FJ-SJ-0000-0115 FJ-RJ-0002 FJ-SJ-0000-0116 FJ-SJ-0000-0117	N/A N/A N/A InValid P/N N/A

Thanks in advance for the help.

 

[mumps]

Are Table 1 and Table 2 on the same sheet? If so, what ranges do the two tables occupy? It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of the tables. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing'
and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[excelvbanoob420]

Are Table 1 and Table 2 on the same sheet? If so, what ranges do the two tables occupy? It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of the tables. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing'
and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

Hello,

Here is the snapshot from XL2BB add in:

Sample Workbook.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	IINPUTCOLUMN	OUTPUTCOLUMN								LookupTable Column1	LookupTable Column2
2	FJ-SJ-0000-0101 FJ-RJ-0000	Valid P/N N/A								FJ-SJ-0000-0101	Valid P/N
3	FJ-SJ-0000-0112 FJ-SJ-0000-0113 FJ-SJ-0000-0114	Valid P/N InValid P/N Valid P/N								FJ-SJ-0000-0112	Valid P/N
4	FJ-RJ-0001 FJ-SJ-0000-0115 FJ-RJ-0002 FJ-SJ-0000-0116 FJ-SJ-0000-0117	N/A N/A N/A InValid P/N N/A								FJ-SJ-0000-0113	InValid P/N
5										FJ-SJ-0000-0114	Valid P/N
6										FJ-SJ-0000-0116	InValid P/N
7
8
9
10
11
Sheet1

Column A is the column of values that I want to lookup. Column J and K is the lookup table.
Intent here is lookup Column A values by matching in Column J and pulling the Column K values as output in Column B.
Hopefully the snapshot is clearer than my description. If my column A had single values in each cell I could just use Vlookup but since each cell in column A contains multiple values, I dont know how to go about looking up these values.
Any help is greatly appreciated!

 

[mumps]

Try:

Sub CompareData()
    Application.ScreenUpdating = False
    Dim i As Long, ii As Long, v As Variant, splitVal As Variant, fnd As Range
    v = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
    For i = LBound(v) To UBound(v)
        splitVal = Split(v(i, 1), Chr(10))
        For ii = LBound(splitVal) To UBound(splitVal)
            Set fnd = Range("J:J").Find(splitVal(ii), LookIn:=xlValues, lookat:=xlWhole)
            If Not fnd Is Nothing Then
                If Range("B" & i + 1) = "" Then
                    Range("B" & i + 1) = fnd.Offset(, 1)
                Else
                    Range("B" & i + 1) = Range("B" & i + 1) & Chr(10) & fnd.Offset(, 1)
                End If
            Else
                If Range("B" & i + 1) = "" Then
                    Range("B" & i + 1) = "N/A"
                Else
                    Range("B" & i + 1) = Range("B" & i + 1) & Chr(10) & "N/A"
                End If
            End If
        Next ii
    Next i
    Application.ScreenUpdating = True
End Sub

 

[excelvbanoob420]

Try:

Sub CompareData()
    Application.ScreenUpdating = False
    Dim i As Long, ii As Long, v As Variant, splitVal As Variant, fnd As Range
    v = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
    For i = LBound(v) To UBound(v)
        splitVal = Split(v(i, 1), Chr(10))
        For ii = LBound(splitVal) To UBound(splitVal)
            Set fnd = Range("J:J").Find(splitVal(ii), LookIn:=xlValues, lookat:=xlWhole)
            If Not fnd Is Nothing Then
                If Range("B" & i + 1) = "" Then
                    Range("B" & i + 1) = fnd.Offset(, 1)
                Else
                    Range("B" & i + 1) = Range("B" & i + 1) & Chr(10) & fnd.Offset(, 1)
                End If
            Else
                If Range("B" & i + 1) = "" Then
                    Range("B" & i + 1) = "N/A"
                Else
                    Range("B" & i + 1) = Range("B" & i + 1) & Chr(10) & "N/A"
                End If
            End If
        Next ii
    Next i
    Application.ScreenUpdating = True
End Sub

Thank you!

 

[mumps]

You are very welcome.