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:
<tbody>
</tbody>
<tbody>
</tbody>P.S my excel is delimited by semicomma so the formula will have to use ";" instead of "," for function
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>
Last edited: