Finding value from cells in one sheet in second sheet where cells contain multiple values

[cob2021]

Hi,

I've tried what feels like everything to extract some values in a column in one worksheet where the cells contain more than just the values i am looking for, from a column in a second worksheet- each column contains thousands of rows.

Here is an example of what i need to do:

Sheet one cells
1122334455
1234554321

Sheet two cells
1234-mat ref 1122334455
mat 4321 1234554321

I want to be able to search for the values in sheet one, in sheet two and either extract the values it finds, or return a Yes (or anything).

Is this possible?

Thanks in advance

 

[DeficientOptimism]

Try this,

It will bring back exactly what matches on sheet 2.

The first part of the match formula here is looking for a partial match, that is why it is contained within *'s.

 

[DeficientOptimism]

Edit - make sure both data sets in sheet one and sheet 2 are formatted as text before doing this formula. You may have sheet one formatted as numbers which may break the match.

 

[cob2021]

Try this,

It will bring back exactly what matches on sheet 2.

The first part of the match formula here is looking for a partial match, that is why it is contained within *'s.


View attachment 30166

Thank you! This has worked, but I realise it's actually the reverse of this that I need - in your example, I want to look in Column I for the numbers in Column E, and return the value from Column E. Is it possible to do it this way round? I've tried to use the formula you've used to do this, but it doesn't seem to do the reverse.

Sorry, I didn't explain it very well to begin!

 

[DeficientOptimism]

I see,

Are the last set of digits in column I always going to be 10 digits long for your data set?

 

[Fluff]

Hi & welcome to MrExcel.
How about one of these options

+Fluff v2.xlsm
	A	B	C	D	E	F
1
2	1234554321 abc 11233	1234554321	1234554321			1122334455
3	1234-mat ref 1122334455	1122334455	1122334455			1234554321
4	mat 4321 1234554321	1234554321	1234554321
5	abc
6
Main

Cell Formulas
Range	Formula
B2:B5	B2	=IFNA(LOOKUP(2,1/(ISNUMBER(SEARCH($F$2:$F$3,A2))),$F$2:$F$3),"")
C2:C5	C2	=FILTER($F$2:$F$3,ISNUMBER(SEARCH($F$2:$F$3,A2)),"")

 

[cob2021]

I see,

Are the last set of digits in column I always going to be 10 digits long for your data set?

Sadly not - they're not always going to be at the end either... sigh

 

[cob2021]

Hi & welcome to MrExcel.
How about one of these options

+Fluff v2.xlsm
	A	B	C	D	E	F
1
2	1234554321 abc 11233	1234554321	1234554321			1122334455
3	1234-mat ref 1122334455	1122334455	1122334455			1234554321
4	mat 4321 1234554321	1234554321	1234554321
5	abc
6
Main

Cell Formulas
Range	Formula
B2:B5	B2	=IFNA(LOOKUP(2,1/(ISNUMBER(SEARCH($F$2:$F$3,A2))),$F$2:$F$3),"")
C2:C5	C2	=FILTER($F$2:$F$3,ISNUMBER(SEARCH($F$2:$F$3,A2)),"")

Thank you, i'm just working through these now. Fingers crossed...

 

[Fluff]

ok

 

[cob2021]

Hi & welcome to MrExcel.
How about one of these options

+Fluff v2.xlsm
	A	B	C	D	E	F
1
2	1234554321 abc 11233	1234554321	1234554321			1122334455
3	1234-mat ref 1122334455	1122334455	1122334455			1234554321
4	mat 4321 1234554321	1234554321	1234554321
5	abc
6
Main

Cell Formulas
Range	Formula
B2:B5	B2	=IFNA(LOOKUP(2,1/(ISNUMBER(SEARCH($F$2:$F$3,A2))),$F$2:$F$3),"")
C2:C5	C2	=FILTER($F$2:$F$3,ISNUMBER(SEARCH($F$2:$F$3,A2)),"")

Hi Fluff, thank you so much, this has worked! I used the first one (IFNA) and after various goes at formatting the cells to match, it has worked!

Thank you - you have made a job I have been putting off for a long time SO much easier!