multiple values in cells vlookup from table list

[Chocho]

Could someone help me what formula to use for below?

I have values separated by commas in sheet 1 column A (about 500 rows of values) and I would like to have a formula that looks up any value in these cells from sheet 1 column A and if it matches with any value in sheet 2 column A (has about about 300 rows of values), to return the value "offshore", otherwise "unknown" in sheet 1's column B.

Sheet 1:

Row/Col	A	B
1	10,11,12	offshore
2	15	offshore
3	47	offshore
4	11,2020	offshore
5	65	unknown
6	899	unknown
7	18,1367,8788	offshore

<tbody>
</tbody>

SHEET 2: in Col A
10
11
12
13
14
15
18
47
56
120
145

<tbody>
</tbody>

P.S my excel is delimited by semicomma so the formula will have to use ";" instead of "," for function

 

[XOR LX]

Hi.

Assuming that the entries in Sheet1 column A are only ever separated by a single comma, as in your examples (and not e.g. by a comma followed by a space), and based on a Sheet2 range of A1:A300 (amend as required), in B1:

=IF(SUMPRODUCT(N(ISNUMBER(SEARCH(","&Sheet2!$A$1:$A$300&",",","&Sheet1!A1&",")))),"offshore","unknown")

Copy down to give similar results for strings in A2, A3, etc.

Regards