How to vlookup when table array columns has multiple values

[ExcelNoob222]

Hi all,

In tab one I have two columns such as:

Item	Value
1234
5478
8978
4587

I want to vlookup data from tab2 into the value column on tab1. Tab2 looks like:

Item	Value
1234, 5478	2
8978, 4587	5

So tab one should look like:

Item
1234	2
5478	2
8978	5
4587	5

Additional Info:

The item column on tab2, may contain more than 2 item numbers per row. Could be 5, or 10 etc. The item numbers will always be the same character count and each row will always be unique (so item 1234 on row 2 will only be on row 2, it will not show up again on say row 10).

Any help would be appreciated!

 

[AhoyNC]

Maybe:
Sheet1

Book1
	A	B
1	Item	Value
2	1234	2
3	5478	2
4	8978	5
5	4587	5
Sheet1

Cell Formulas
Range	Formula
B2:B5	B2	=FILTER(Sheet2!$B$2:$B$3,ISNUMBER(SEARCH(A2,Sheet2!$A$2:$A$3)),"No Match")

Sheet2

Book1
	A	B
1	Item	Value
2	1234, 5478	2
3	8978, 4587	5
Sheet2

 

[ExcelNoob222]

Maybe:
Sheet1

Book1
	A	B
1	Item	Value
2	1234	2
3	5478	2
4	8978	5
5	4587	5
Sheet1

Cell Formulas
Range	Formula
B2:B5	B2	=FILTER(Sheet2!$B$2:$B$3,ISNUMBER(SEARCH(A2,Sheet2!$A$2:$A$3)),"No Match")

Sheet2

Book1
	A	B
1	Item	Value
2	1234, 5478	2
3	8978, 4587	5
Sheet2

Hi!

That is awesome, worked perfectly. Thank you for your help. Are you able to explain how this works??

 

[AhoyNC]

You're welcome.
The SEARCH function looks for the item. If it finds it it will return a number which is the starting position in the string where it found the item.
So, for cell B2 in the example

SEARCH(A2,Sheet2!$A$2:$A$3) will return {1;#VALUE!}

ISNUMBER will return either a TRUE or FALSE to the FILTER function

ISNUMBER(SEARCH(A2,Sheet2!$A$2:$A$3)) will return {TRUE;FALSE}

Then FILTER will return the value in B2 of sheet2 since there is a TRUE in the first row.

 

[ExcelNoob222]

You're welcome.
The SEARCH function looks for the item. If it finds it it will return a number which is the starting position in the string where it found the item.
So, for cell B2 in the example

SEARCH(A2,Sheet2!$A$2:$A$3) will return {1;#VALUE!}

ISNUMBER will return either a TRUE or FALSE to the FILTER function

ISNUMBER(SEARCH(A2,Sheet2!$A$2:$A$3)) will return {TRUE;FALSE}

Then FILTER will return the value in B2 of sheet2 since there is a TRUE in the first row.

Great, thanks for providing explanation.