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

 

[Eric W]

If you have Excel 365, you can do something like this:

Book1
	A	B	C	D	E	F	G	H
1		Code(s)	Product(s)				Code	Product
2		1047, 1382, 1755	Chair, Table, Book				1111	Pencil
3		1111	Pencil				2222	Sofa
4		9999	??				1047	Chair
5		1111, 2222, 3333, 4444, 5555, 6666, 1047	Pencil, Sofa, Desk, Picture, Tree, ??, Chair				3333	Desk
6							1755	Book
7							4444	Picture
8							1382	Table
9							5555	Tree
10
Sheet13

Cell Formulas
Range	Formula
C2:C5	C2	=TEXTJOIN(", ",,XLOOKUP(0+MID(SUBSTITUTE(B2,",",REPT(" ",100)),SEQUENCE(LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1,,1,100),100),$G$2:$G$10,$H$2:$H$10,"??",0))

If you don't have Excel 365, you're out of luck, at least as far as formulas go. It requires some new features. If you have an older version, we can write a UDF that should work for you, if you're ok with having a macro.

 

[Ucubia]

Brilliant!! Thank you so much for your help!

 

[Eric W]

Happy to help! ?

 

[JoeMo]

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

 

[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

Thanks for the code, also have had assistance which is formula based and like to find favour with the customer

Great code and many thanks for taking the time

 

[JoeMo]

Thanks for the code, also have had assistance which is formula based and like to find favour with the customer

Great code and many thanks for taking the time

You are welcome - thanks for the reply.

 

[Ucubia]

If you have Excel 365, you can do something like this:

Book1
	A	B	C	D	E	F	G	H
1		Code(s)	Product(s)				Code	Product
2		1047, 1382, 1755	Chair, Table, Book				1111	Pencil
3		1111	Pencil				2222	Sofa
4		9999	??				1047	Chair
5		1111, 2222, 3333, 4444, 5555, 6666, 1047	Pencil, Sofa, Desk, Picture, Tree, ??, Chair				3333	Desk
6							1755	Book
7							4444	Picture
8							1382	Table
9							5555	Tree
10
Sheet13

Cell Formulas
Range	Formula
C2:C5	C2	=TEXTJOIN(", ",,XLOOKUP(0+MID(SUBSTITUTE(B2,",",REPT(" ",100)),SEQUENCE(LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1,,1,100),100),$G$2:$G$10,$H$2:$H$10,"??",0))

If you don't have Excel 365, you're out of luck, at least as far as formulas go. It requires some new features. If you have an older version, we can write a UDF that should work for you, if you're ok with having a macro.

Hi Eric, came across a problem when the asset detail within a cell becomes larger, for example there is a room with 34 asset categories. What seems to be happening, the assets are being captured but dropping the last asset ID when it comes to the xlookup. For example, in one of the cells there are 18 categories, the last 3 being 104501, 104584, 900001 - using evaluate formula everything looks fine, until the xlookup is executed when I can see 104501, 104584 only. In another example the last 3 IDs in the xlookup would be 104501, 1045.

As I said in the main the formula works fine, is there anything that could explain the anomalies ??

I would forward the data but its quite large and unfortunately information is sensitive

Thanks

 

[Eric W]

The formula uses a trick to separate the entries. It converts the commas to a string of 100 spaces, then looks at each 100-character section of the result, and each section should have 1 of the entries in it. The problem comes in when the total number of non-space characters in the original cell is over 100. Then the entries get shifted, and some of the later entries get shifted into other sections, or even overlap sections. I did not expect that you'd have so many entries per cell. One thing you can try is to increase the size of the sections to 200, like so:

Book1
	A	B	C	D	E	F	G	H
1		Code(s)	Product(s)				Code	Product
2		1047, 1382, 1755	Chair, Table, Book				1111	Pencil
3		1111	Pencil				2222	Sofa
4		9999	??				1047	Chair
5		1111, 2222, 3333, 4444, 5555, 6666, 1047	Pencil, Sofa, Desk, Picture, Tree, ??, Chair				3333	Desk
6							1755	Book
7							4444	Picture
8							1382	Table
9							5555	Tree
Sheet13

Cell Formulas
Range	Formula
C2:C5	C2	=TEXTJOIN(", ",,XLOOKUP(0+MID(SUBSTITUTE(B2,",",REPT(" ",200)),SEQUENCE(LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1,,1,200),200),$G$2:$G$10,$H$2:$H$10,"??",0))

However, you can't keep raising that value indefinitely. There are other limitations of the functions in the formula. 255 seems to be the max. You might consider using Joe's UDF, it does not have the same limitation.

 

[Ucubia]

The formula uses a trick to separate the entries. It converts the commas to a string of 100 spaces, then looks at each 100-character section of the result, and each section should have 1 of the entries in it. The problem comes in when the total number of non-space characters in the original cell is over 100. Then the entries get shifted, and some of the later entries get shifted into other sections, or even overlap sections. I did not expect that you'd have so many entries per cell. One thing you can try is to increase the size of the sections to 200, like so:

Book1
	A	B	C	D	E	F	G	H
1		Code(s)	Product(s)				Code	Product
2		1047, 1382, 1755	Chair, Table, Book				1111	Pencil
3		1111	Pencil				2222	Sofa
4		9999	??				1047	Chair
5		1111, 2222, 3333, 4444, 5555, 6666, 1047	Pencil, Sofa, Desk, Picture, Tree, ??, Chair				3333	Desk
6							1755	Book
7							4444	Picture
8							1382	Table
9							5555	Tree
Sheet13

Cell Formulas
Range	Formula
C2:C5	C2	=TEXTJOIN(", ",,XLOOKUP(0+MID(SUBSTITUTE(B2,",",REPT(" ",200)),SEQUENCE(LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1,,1,200),200),$G$2:$G$10,$H$2:$H$10,"??",0))

However, you can't keep raising that value indefinitely. There are other limitations of the functions in the formula. 255 seems to be the max. You might consider using Joe's UDF, it does not have the same limitation.

Hi Eric - great thanks again.

Kind of limited at customer site as to what I can do in terms of macros etc hence the formula being the desired approach. Have “played” with the adjustments before emailing and didn’t work so guess that’s the limit for formula. I did try the “200” but that resulted in #VALUE errors. Trim’d the data just to make sure but not change.

I did try then macro on my own machine, again some success tempered with unexpected results - I repeated a Trim of the data to make sure there were no funny characters but no immediate positive result.

Odd but will keep at it as right now something with 80% accuracy is 100% more than customer has.

Thank you so much for your help