Search a multiple value cell against an array

[GS0982]

I'm trying to find out how to use a formula to search cells containing multiple values against an array of values and either extract those values to another column or just return some identifier (not exist v exist, etc.). Is this possible?

ex:
target cell values are in this format

860032011
830001267
860008007
860007027
860010022
830006025
112101011
112103012


versus my list of values for reference

112101014
112101039
112101056
112101057
112102003
112102004
112101055
112101090

<tbody>
</tbody>

 

[GS0982]

I've tried this =IF(ISERROR(VLOOKUP(Q7P6:P1055,WORKING!A1:A53,1,FALSE)),"NOT EXIST","EXIST") and it "works", but does not search the entire cell

 

[DanteAmor]

You could explain a single example, what to look for and where to look. And in that example what is the result.

Is this in a single cell?

target cell values are in this format


860032011
830001267
860008007
860007027
860010022
830006025
112101011
112103012

 

[GS0982]

Yes it is in a single cell.

Basically, I have a cell with 10 multiple response values (stacked with carriage returns).

Instead of splitting those values out to separate cells, I want to know if I can leave the cell as it is and also search it against a table of values.

If I have these department IDs all in the same cell and separated by a carriage return:

1000
1001
1002
1003
1004
1005

I want to know if I can compare it against a range of values in individual cells in another sheet:

Reference ex:

8888

29211

30341

1003

4522

And either highlight the cell, or display some match value, etc

 

[DanteAmor]

If I understood well

Excel Workbook
	A
1	8888
2	29211
3	30341
4	1003
5	4522
6	112101014
7	112101039
8	112101056
9	112101057
10	112102003
11	112102004
12	112101055
13	112101090
Sheet WORKING

Excel Workbook
	A	B
1	ID	Reference (Row Number)
2	100010011002100310041005	4
3	860032011830001267860008007860007027860010022830006025112101011112103012	0
4	860032011112101039860008007860007027860010022830006025112101011112103012	7
Sheet1