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]

I prefer to keep things simple!

Fair enough.

 

[Marcol]

I'll go with Biff here,

For all practical purposes 99^99 can be used rather than 9.99999999999999E+307

Unless of course somebody knows a real situation, in Excel, where it fails ...

 

[T. Valko]

Unless of course somebody knows a real situation, in Excel, where it fails ...

Every time I put out that challenge all I get is crickets!

 

[circledchicken]

I'll go with Biff here,

For all practical purposes 99^99 can be used rather than 9.99999999999999E+307

Unless of course somebody knows a real situation, in Excel, where it fails ...

Every time I put out that challenge all I get is crickets!

I understand, although for me the point is about being consistent rather than any practical risk of failure because a smaller 'huge' number is selected. Anything large - 100^99 or 99^98 or 1E100 - is equally appropriate, but 9.99999999999999E+307 seems like a good natural choice as the largest allowed positive number.

Used consistently, the number becomes a self-documenting feature. It abstracts away the need to come up with a suitable big number for every situation, and perhaps some confusion is avoided down the line.

In my opinion, choosing arbitrarily large numbers every time you come across the same class of problem might save a few keystrokes but introduces unnecessary complexity. For me, keeping it simple, means sticking to 9.99999999999999E+307.

 

[T. Valko]

unnecessary complexity ... 9.99999999999999E+307.

Hmmm...

And the AVERAGE user doesn't think 9.99999999999999E+307 is unnecessary complexity?

Quick, without counting them how many 9s do I have to type?

If I want to return my last bowling score from column A...

Take your pick.

=LOOKUP(301,A:A)
=LOOKUP(9.99999999999999E+307,A:A)

Both will return the correct result. I know which one I'll use!

 

[circledchicken]

Hmmm...

And the AVERAGE user doesn't think 9.99999999999999E+307 is unnecessary complexity?

Quick, without counting them how many 9s do I have to type?

If I want to return my last bowling score from column A...

Take your pick.

=LOOKUP(301,A:A)
=LOOKUP(9.99999999999999E+307,A:A)

Both will return the correct result. I know which one I'll use!

Unfortunately, I am not convinced - my pick is:
=LOOKUP(9.99999999999999E+307,A:A)

I don't think the speed at which you can deliver the formula to the cell is really an issue here. As the function is being exploited in a non-standard and non-documented way, I think either version will be confusing to the average user, more so version one. A quick Google search of the latter brings up a host of relevant results. A Google search of the former leaves you none the wiser.

 

[T. Valko]

the function is being exploited in a non-standard and non-documented way.

Yet you want to convince us that we should use some sort of "standard" lookup value?

What sanctioning committee has established this "standard" ?

Well, like I said...

I prefer to keep things simple!

I also don't like typing! The more you type the greater the chance of making errors.

 

[circledchicken]

Yet you want to convince us that we should use some sort of "standard" lookup value?

Yes, I think that would be wise.

Well, like I said...

I prefer to keep things simple!

Me too!

I also don't like typing! The more you type the greater the chance of making errors.

True, and I have made those errors - but I don't think that should override the other points.

Anyway, I guess we all have our preferences and maybe one day I will be convinced by your argument. I should also say that I do really appreciate all your wonderful help on this forum, as I have learnt a great deal from many of your posts.

 

[T. Valko]

Yes, I think that would be wise.


Me too!


True, and I have made those errors - but I don't think that should override the other points.

Anyway, I guess we all have our preferences and maybe one day I will be convinced by your argument. I should also say that I do really appreciate all your wonderful help on this forum, as I have learnt a great deal from many of your posts.

When I first learned about that LOOKUP application I also used a bazillion 9s as the lookup value but I am much wiser now-a-days!