Lookup text or number string inside cell Excel 2010

[Oz-Six-Oz]

Hello everyone,

Okay, I'm new here and have no experience in Excel. I have tryed the forum, internet and Excel help without luck (Maybe don't know how to search), I want to do the normal lookup but searching a number or text string inside a row full of text.

For Example, I have in Sheet1:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]p6769705[/TD]
[TD]Lot# * 2-3182 ID lot 50643000[/TD]
[TD]="FORMULA HERE"[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]p6769713[/TD]
[TD]** 2-3166 Filled with lot-50642992[/TD]
[TD]="FORMULA HERE"[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]p6769756[/TD]
[TD]* 2-3182 LOT 50642917[/TD]
[TD]="FORMULA HERE"[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Now in sheet2:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2-3182[/TD]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2-3186[/TD]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3221545[/TD]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



I want to lookup value in Sheet2 A1 inside Sheet1 column B:B and then return in Sheet1 C:C the content in Sheet2 column B:B, is this possible?

For example for the first row in sheet1 C1 the result should be 11, since B1 has inside the cell value noted in Sheet 2 A1 "2-3182".

Please if you have the formula please explain it to me in detail how it works to understand it and apply it in the future, don't want just the formula to resolve this but to learn how to do it in the future understanding each part of the formula.

Tried Lookup and VLookup but these only search for exact cell, true? sorry if my question is dumb but i have tried everything and i'm frustrated with a headache now!

If a code is required then send me a link on how to make a code for dummies to not explain everything to me

Any help will be greatly appreciated

Thanks,

Oz

 

[circledchicken]

Hi Oz and welcome to the forum,

With Sheet2 as shown in your example, perhaps try this formula in C1 of Sheet1 (I have amended some of the values to better represent the results). Adjust the ranges as appropriate and drag down the formula as far as required:

Sheet1[TABLE="class: html-maker-worksheet"]
<tbody>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[/TR]
[TR]
[TH]1[/TH]
[TD]p6769705[/TD]
[TD]Lot# * 2-3182 ID lot 50643000[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TH]2[/TH]
[TD]p6769756[/TD]
[TD]* 2-3186 LOT 50642917[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD]p6769758[/TD]
[TD="align: right"]3221545[/TD]
[TD="align: right"]11[/TD]
[/TR]
</tbody>[/TABLE]
Excel 2010

Cell	Formula
C1	=LOOKUP(2, 1/SEARCH(Sheet2!A$1:A$3,B1), Sheet2!B$1:B$3)

<tbody>
[TD="bgcolor: #FFFFFF"] Worksheet Formulas [TABLE="class: html-maker-worksheet"]
<tbody>[TR]

</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]
Note:

• This formula setup is explained in great detail by Aladin Akyurek here: http://www.mrexcel.com/forum/excel-questions/99621-lookup-value-unsorted-data.html#post492425
• The only slight difference being the SEARCH function used in the lookup_vector argument. Here is a concise explanation of how SEARCH works: MS Excel: SEARCH Function (WS)
• Since you mentioned you are new to Excel, the following notes on range referencing may also be of use:
  • Office 2010 Class #25: Excel Cell References: Relative, Absolute, Mixed - YouTube
  • Switch between relative, absolute, and mixed references - Excel - Office.com
  • Relative And Absolute Range References

 

[Oz-Six-Oz]

This is Magic!!! thanks Circled for the great help, one day I will be a master like you! Nice links, they explain everything in detail there but I'm less familiar to excel concepts plus my english is not perfect so to understand them I will take some classes before Christmas...

 

[circledchicken]

This is Magic!!! thanks Circled for the great help, one day I will be a master like you! Nice links, they explain everything in detail there but I'm less familiar to excel concepts plus my english is not perfect so to understand them I will take some classes before Christmas...

You're welcome - thanks for the feedback! (I'm no master haha - but I hope you will be one day!)

 

[Aladin Akyurek]

...

Cell	Formula
C1	=LOOKUP(2, 1/SEARCH(Sheet2!A$1:A$3,B1), Sheet2!B$1:B$3)

<tbody>
[TD="bgcolor: #FFFFFF"]
Worksheet Formulas [TABLE="class: html-maker-worksheet"]
<tbody>[TR]

</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]
[...]

We could avoid the division though...

=LOOKUP(9.99999999999999E+307,SEARCH(Sheet2!A$1:A$3,B1),Sheet2!B$1:B$3)

 

[circledchicken]

We could avoid the division though...

=LOOKUP(9.99999999999999E+307,SEARCH(Sheet2!A$1:A$3,B1),Sheet2!B$1:B$3)

Aladin, on the other hand, is a master

 

[Oz-Six-Oz]

You guys are Great!, Aladin, thanks for your time and for spreading the knowledge with us. Do you want to read something funny? I'm the most experienced excel user in my company... (LOL), so i will be be posting some more questions, for sure i'm struggling 3 or 4 days with something before asking guys , with this help in this thread you save me like 8 hours of so manual work with Excel (I know what i'm talking about since i did it with a previous file). Conclusion, is great to have you here Circled/Aladin!

 

[T. Valko]

We could avoid the division though...

=LOOKUP(9.99999999999999E+307,SEARCH(Sheet2!A$1:A$3,B1),Sheet2!B$1:B$3)

We can also avoid that real long number...

=LOOKUP(32768,SEARCH(Sheet2!A$1:A$3,B1),Sheet2!B$1:B$3)

 

[circledchicken]

You guys are Great!, Aladin, thanks for your time and for spreading the knowledge with us. Do you want to read something funny? I'm the most experienced excel user in my company... (LOL), so i will be be posting some more questions, for sure i'm struggling 3 or 4 days with something before asking guys , with this help in this thread you save me like 8 hours of so manual work with Excel (I know what i'm talking about since i did it with a previous file). Conclusion, is great to have you here Circled/Aladin!

Thanks Oz! I'm glad it saved you some time.

We can also avoid that real long number...

=LOOKUP(32768,SEARCH(Sheet2!A$1:A$3,B1),Sheet2!B$1:B$3)

T. Valko - also a master!

I've seen a few threads where you and Aladin discuss 9.99999999999999E+307. Personally, I prefer using the long number and think it is a reasonable choice as a convention for this class of formulae.

I think it helps to keep to a convention for these types of problems and the benefits outweigh the perceived 'ugliness'. It helps with maintainability and understandability in the long-term over, for example, arbitrarily large numbers that suit each situation which can conceal the underlying intent. Sticking to it as a standard may also mean people who see this type of formulae for the first time can find help a little more easily.

That's just my view, for now at least - and not that either of you care or should care about it!

If anyone is interested, there is an article by Douglas Crockford that is very loosely related to this topic but talks a little bit about why programming (JavaScript) style is important. The first few general paragraphs are maybe somewhat applicable here:
The Elements of JavaScript Style

 

[T. Valko]

Thanks Oz! I'm glad it saved you some time.


T. Valko - also a master!

I've seen a few threads where you and Aladin discuss 9.99999999999999E+307. Personally, I prefer using the long number and think it is a reasonable choice as a convention for this class of formulae.

I think it helps to keep to a convention for these types of problems and the benefits outweigh the perceived 'ugliness'. It helps with maintainability and understandability in the long-term over, for example, arbitrarily large numbers that suit each situation which can conceal the underlying intent. Sticking to it as a standard may also mean people who see this type of formulae for the first time can find help a little more easily.

That's just my view, for now at least - and not that either of you care or should care about it!

If anyone is interested, there is an article by Douglas Crockford that is very loosely related to this topic but talks a little bit about why programming (JavaScript) style is important. The first few general paragraphs are maybe somewhat applicable here:
The Elements of JavaScript Style

I prefer to keep things simple!