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

cob2021

New Member
Joined
Jan 20, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

DeficientOptimism

New Member
Joined
Jan 19, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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.


1611144937089.png
 

DeficientOptimism

New Member
Joined
Jan 19, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 20, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 19, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I see,

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about one of these options
+Fluff v2.xlsm
ABCDEF
1
21234554321 abc 11233123455432112345543211122334455
31234-mat ref 1122334455112233445511223344551234554321
4mat 4321 123455432112345543211234554321
5abc  
6
Main
Cell Formulas
RangeFormula
B2:B5B2=IFNA(LOOKUP(2,1/(ISNUMBER(SEARCH($F$2:$F$3,A2))),$F$2:$F$3),"")
C2:C5C2=FILTER($F$2:$F$3,ISNUMBER(SEARCH($F$2:$F$3,A2)),"")
 
Solution

cob2021

New Member
Joined
Jan 20, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Jan 20, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about one of these options
+Fluff v2.xlsm
ABCDEF
1
21234554321 abc 11233123455432112345543211122334455
31234-mat ref 1122334455112233445511223344551234554321
4mat 4321 123455432112345543211234554321
5abc  
6
Main
Cell Formulas
RangeFormula
B2:B5B2=IFNA(LOOKUP(2,1/(ISNUMBER(SEARCH($F$2:$F$3,A2))),$F$2:$F$3),"")
C2:C5C2=FILTER($F$2:$F$3,ISNUMBER(SEARCH($F$2:$F$3,A2)),"")
Thank you, i'm just working through these now. Fingers crossed...
 

cob2021

New Member
Joined
Jan 20, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about one of these options
+Fluff v2.xlsm
ABCDEF
1
21234554321 abc 11233123455432112345543211122334455
31234-mat ref 1122334455112233445511223344551234554321
4mat 4321 123455432112345543211234554321
5abc  
6
Main
Cell Formulas
RangeFormula
B2:B5B2=IFNA(LOOKUP(2,1/(ISNUMBER(SEARCH($F$2:$F$3,A2))),$F$2:$F$3),"")
C2:C5C2=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!
 

Watch MrExcel Video

Forum statistics

Threads
1,128,150
Messages
5,628,992
Members
416,358
Latest member
grsaltzman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top