Multiple lookup from single cell

[Ucubia]

Hi,

I have been given some Excel data whereupon a cell will contain multiple asset tags separated by a comma.

Is it possible to perform a formula "lookup" of some description to return the item associated with the asset tag from another sheet and also separated with a comma

For example B2 contains tag 1047, 1382, 1755

In the Product sheet:
1047 Chair
1382 Table
1755 Book

The desired result being B2 cell 1047,1382,1755 and cell C2 resulting in Chair, Table, Book

Many thanks in advance

Ian

 

[Ucubia]

For those not running a version of Excel that includes the worksheet functions that Eric uses, here's a UDF that works.

Book1
	B	C	D	E	F	G
1	Asset Tags	Assets			Asset Tag	Asset
2	1047, 1382, 1755	Chair, Table, Book			1600	locker
3	1382, 1047, 9000	Table, Chair, N/A			1755	Book
4	1600, 9000, 1382	locker, N/A, Table			2022	Gloves
5					1047	Chair
6					1382	Table
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=GetAsset(B2,$F$2:$G$6)

Function GetAsset(S As String, lkupRng As Range) As String
Dim V As Variant, i As Long, X As Variant
V = Split(S, ", ")
For i = LBound(V) To UBound(V)
    On Error Resume Next
    X = Application.VLookup(CLng(V(i)), Range(lkupRng.Address), 2, False)
    On Error GoTo 0
    If Not IsError(X) Then
        GetAsset = GetAsset & ", " & X
    Else
        GetAsset = GetAsset & ", " & "N/A"
    End If
Next i
GetAsset = Mid(GetAsset, 3, Len(GetAsset))
End Function

Hi Joe,

Does this code work when the reference points to a different sheet? In the example the lookup data is on the same page, but I am wondering if instead of $F$2:$G$6 something like File!$F$2:$G$6 will work or will I need additional code - I have tried but get a "N/A" when I know there will be an actual value.

Apologies coding is not something I do regularly and by that I mean "only in dire emergency"

Thanks

regards, Ian

 

[JoeMo]

Here's a modification to allow for the lookup table being on a different sheet, in this example Sheet2.

Book1
	A	B
5	89, 20, 68	Al, Ant, Mike
Sheet1

Cell Formulas
Range	Formula
B5	B5	=GetAsset(A5,Sheet2!$D$2:$E$4)

Book1
	D	E
1	Value	Name
2	89	Al
3	68	Mike
4	20	Ant
Sheet2

Function GetAsset(S As String, lkupRng As Range) As String
Dim V As Variant, i As Long, X As Variant
V = Split(S, ", ")
For i = LBound(V) To UBound(V)
    On Error Resume Next
    X = Application.VLookup(CLng(V(i)), Range("'" & lkupRng.Parent.Name & "'!" & lkupRng.Address), 2, False)
    On Error GoTo 0
    If Not IsError(X) Then
        GetAsset = GetAsset & ", " & X
    Else
        GetAsset = GetAsset & ", " & "N/A"
    End If
Next i
GetAsset = Mid(GetAsset, 3, Len(GetAsset))
End Function

 

[Ucubia]

Here's a modification to allow for the lookup table being on a different sheet, in this example Sheet2.

Book1
	A	B
5	89, 20, 68	Al, Ant, Mike
Sheet1

Cell Formulas
Range	Formula
B5	B5	=GetAsset(A5,Sheet2!$D$2:$E$4)

Book1
	D	E
1	Value	Name
2	89	Al
3	68	Mike
4	20	Ant
Sheet2

Function GetAsset(S As String, lkupRng As Range) As String
Dim V As Variant, i As Long, X As Variant
V = Split(S, ", ")
For i = LBound(V) To UBound(V)
    On Error Resume Next
    X = Application.VLookup(CLng(V(i)), Range("'" & lkupRng.Parent.Name & "'!" & lkupRng.Address), 2, False)
    On Error GoTo 0
    If Not IsError(X) Then
        GetAsset = GetAsset & ", " & X
    Else
        GetAsset = GetAsset & ", " & "N/A"
    End If
Next i
GetAsset = Mid(GetAsset, 3, Len(GetAsset))
End Function

Awesome!! Just absolutely awesome! Tested and works a treat! Thank you so much and equally I can use a similar approach for something else that has just dropped into the inbox.

Really appreciate your help and patience.

 

[JoeMo]

Awesome!! Just absolutely awesome! Tested and works a treat! Thank you so much and equally I can use a similar approach for something else that has just dropped into the inbox.

Really appreciate your help and patience.

You are welcome - thanks for the reply.

 

[Matafy]

For those not running a version of Excel that includes the worksheet functions that Eric uses, here's a UDF that works.

Book1
	B	C	D	E	F	G
1	Asset Tags	Assets			Asset Tag	Asset
2	1047, 1382, 1755	Chair, Table, Book			1600	locker
3	1382, 1047, 9000	Table, Chair, N/A			1755	Book
4	1600, 9000, 1382	locker, N/A, Table			2022	Gloves
5					1047	Chair
6					1382	Table
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=GetAsset(B2,$F$2:$G$6)

Function GetAsset(S As String, lkupRng As Range) As String
Dim V As Variant, i As Long, X As Variant
V = Split(S, ", ")
For i = LBound(V) To UBound(V)
    On Error Resume Next
    X = Application.VLookup(CLng(V(i)), Range(lkupRng.Address), 2, False)
    On Error GoTo 0
    If Not IsError(X) Then
        GetAsset = GetAsset & ", " & X
    Else
        GetAsset = GetAsset & ", " & "N/A"
    End If
Next i
GetAsset = Mid(GetAsset, 3, Len(GetAsset))
End Function

I used this, but the results produced nothing. Need help. I need to use the entire invoice number to reference the status.

 

[JoeMo]

I used this, but the results produced nothing. Need help. I need to use the entire invoice number to reference the status.

View attachment 57747View attachment 57748

Please provide some additional information including a detailed description of what you want to do, some sample data using XL2BB and the desired result you want for the sample data.

 

[Matafy]

This one is not accepting the "-A" or the "-B" when I run this function...

Load Plan.xlsm
	B	C
2	38319549-B, 38319625-A, 38319612-A, 38327534-B, 38327521-A	, , , ,
3
4	Pick Ticket#	P/T Status
5	38319549-B	Pick Pending
6	38319625-A	Load Pending
7	38319612-A	Load Pending
8	38327534-B	Pick Pending
9	38327521-A	Pick Pending
Sheet1

Cell Formulas
Range	Formula
C2	C2	=GetInvoiceStatus(B2,B5:C9)

But when I remove the "-A" characters it works flawlessly.

Load Plan.xlsm
	B	C
1	Pick Ticket#	P/T Status
2	38319549, 38319625, 38319612, 38327534, 38327521	Pick Pending, Load Pending, Load Pending, Pick Pending, Pick Pending
3
4	Pick Ticket#	P/T Status
5	38319549	Pick Pending
6	38319625	Load Pending
7	38319612	Load Pending
8	38327534	Pick Pending
9	38327521	Pick Pending
Sheet1

Cell Formulas
Range	Formula
C2	C2	=GetInvoiceStatus(B2,B5:C9)

I need the "-A" in the both "Pick Ticket#."

 

[JoeMo]

This one is not accepting the "-A" or the "-B" when I run this function...

The function was created in response to the OP which used numeric inputs to the single cell. Here's a modification that allows for text values in the single cell:

Book1
	B	C
2	38319549-B, 38319625-A, 38319612-A, 38327534-B, 38327521-A	Pick Pending, Load Pending, Load Pending, Pick Pending, Pick Pending
3
4	Pick Ticket#	P/T Status
5	38319549-B	Pick Pending
6	38319625-A	Load Pending
7	38319612-A	Load Pending
8	38327534-B	Pick Pending
9	38327521-A	Pick Pending
Sheet3

Cell Formulas
Range	Formula
C2	C2	=GetInvoiceStatus(B2,B5:C9)

Function GetInvoiceStatus(S As String, lkupRng As Range) As String
Dim V As Variant, i As Long, X As Variant
V = Split(S, ", ")
For i = LBound(V) To UBound(V)
    On Error Resume Next
    X = Application.VLookup(V(i), Range("'" & lkupRng.Parent.Name & "'!" & lkupRng.Address), 2, False)
    On Error GoTo 0
    If Not IsError(X) Then
        GetInvoiceStatus = GetInvoiceStatus & ", " & X
    Else
        GetInvoiceStatus = GetInvoiceStatus & ", " & "N/A"
    End If
Next i
GetInvoiceStatus = Mid(GetInvoiceStatus, 3, Len(GetInvoiceStatus))
End Function

 

[Matafy]

This worked great.

Thank you!

 

[JoeMo]

This worked great.

Thank you!

You are welcome - thanks for the reply.