Microsoft Excel Help Needed - Find and Extract Data from Cell

I have two spreadsheets:

1st spreadsheet contains zip code and a reference number in two different cells (e.g. Column A contains "Ticket Number" Column B contains "Zip Code")
2nd spreadsheet contains text in one cell that includes both "Ticket Number" and "Zip Code" along with a description of job to performed (Column J)

I'm trying to build one spreadsheet that is capable of looking up and referencing Spreadsheet One "Ticket Number" and "Zip Code" against Spreadsheet Two (Column J) and Extracting the "Zip Code" and "Ticket Number" only and keep it within Spreadsheet One.

For example:  
Spreadsheet One contains zip code 64128 in cell B3 and Ticket Number C5698065397 in cell B4
Spreadsheet Two cell J468 text reads:  "Install Microsoft Server and build out according to customer specs.  Job is located in Chicago, IL  60628 please ask for John Fields upon arriving.  Customer reference number is C5698065397, due date for job to be completed is 05-Jan-2012 at 23:59"

How can I find and reference zip code and ticket number as contained in spreadsheet one against column J in Spreadsheet two?

1.  The ticket number is never the same length, some ticket numbers range from 3 characters to 20 characters, they are never the same length.
dccexpertAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

barry houdiniCommented:
Hello dccexpert, I don't understand the significance of the zipcode - you say that B4 contains 64128 in your example, yet J468 only contains zipcode 60628 - clearly those aren't a match, is that an error in your description or are you expecting a match on the ticket number only?

regards, barry
0
dccexpertAuthor Commented:
Hello barryhoudini yes that is an error in my description above both fields contain the same zip code
0
barry houdiniCommented:
OK, but I'm not clear what you want to return. If there's a match on both zipcode and customer number do you want to return that text from J468? With everything on one sheet for simplicity you could use a formula like this

=LOOKUP(2,1/ISNUMBER(SEARCH(B3,J2:J100)*SEARCH(B4,J2:J100)),J2:J100)

see attached example with formula in B5

regards, barry
27520003.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dccexpertAuthor Commented:
Exactly what I was looking for thank you very much!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.