How to use VLOOKUP if lookup values are comma separated in a single cell?

[svidmar]

Hi

I have a question that I can't seem to find a working solution for

In sheet #1 I have a list of ISSN numbers in Column A:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ISSN[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0904-3535[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]0941-4355, 1433-7339[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2076-0787[/TD]
[/TR]
</tbody>[/TABLE]

And in sheet #2 I have the following in columns A and B:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ISSN[/TD]
[TD]URL[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0904-3535[/TD]
[TD]www.example.com[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1433-7339[/TD]
[TD]www.example1.com[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2076-0787[/TD]
[TD]www.example2.com[/TD]
[/TR]
</tbody>[/TABLE]

Task: I need to create a list of URLs in sheet#1 based on ISSN numbers.

I use the VLOOKUP function to look in sheet #2 after the URL's to the corresponding ISSN number. It works fine, when there is only one value in each cell in column A (sheet #1 ) . The problem arises when there are several values in the same cell, separated by a comma (cell A3). The value AFTER the comma is being ignored by the function. I can't figure out how to, as an example, have the VLOOKUP function return "www.example1.com' based on the ISSN '1433-7339', because the value is after the comma in the cell.

I'm "not allowed" to alter the column with the comma separated values, so it's not an option to split the column.

Any ideas on how to do this? Should I use another function, in stead of VLOOKUP?

Any help would be greatly appreciated

 

[Kamolga]

I can't test it but what about

 =[URL="https://exceljet.net/excel-functions/excel-vlookup-function"]VLOOKUP[/URL]("*"&$H$2&"*",$A$2:$B$4,2,FALSE)

where H2 is value you are looking for?

 

[Aladin Akyurek]

In B2 of Sheet1 control+shift+enter, not just enter, copy across, and down:

=IFERROR(INDEX(Sheet2!$B$2:$B$4,SMALL(IF(ISNUMBER(SEARCH(SUBSTITUTE(Sheet2!$A$2:$A$4," ","")&",",SUBSTITUTE($A2," ","")&",")),ROW(Sheet2!$A$2:$B$4)-ROW(INDEX(Sheet2!$A$2:$B$4,1,1))+1),COLUMNS($B2:B2))),"")

 

[Peter_SSs]

The standard-entry formula shown below should return multiple results in the corresponding order to the multiple items in column A of Sheet1 and it should not be upset by any blank rows in the table in Sheet2.

Book1
	A	B
1	ISSN	URL
2	0904-3535	www.example.com
3
4	1433-7339	www.example1.com
5	0941-4355	www.example3.com
6	2076-0787	www.example2.com
Sheet2

Formula in B2 is copied across and down.

Book1
	A	B	C	D
1	ISSN
2	0904-3535	www.example.com
3	0941-4355, 1433-7339	www.example3.com	www.example1.com
4	2076-0787	www.example2.com
Sheet1

Cell Formulas
Range	Formula
B2	=IFERROR(VLOOKUP(TRIM(MID(SUBSTITUTE($A2,",",REPT(" ",100)),COLUMNS($B:B)*100-99,100)),Sheet2!$A$2:$B$10,2,0),"")