Complicated lookup

[Doug]

Can anyone help please.
I am trying to create a formula that can lookup only part
of a cell.
The formula i'm looking for is:If 00288849,(C1),is equal
to 0028849,(B1),return A1,(fred)

This is an actual example of how the cells look.

A1: FRED

B1: Order: 0028849||||||

C1 : Advice note: 00288849/001

All the other cells in columns B and C are in exactly the
same order with corresponding numbers in the same
place,so ideally the lookup should be ,"select the first
8 numbers only",or "select the first 8 numbers starting 8
characters in,(including space)"(B1),0r 14 characters in,
(C1).
If i cannot lookup ,do you know a formula that will
seperate the numbers 00288849 and return them to another
cell on their own ?

Doug

 

[Don]

Doug

I'm not sure I understand the problem, but this formula will look down the
rows in Columns B and C until both equal the value in D1 (the value in D1
can be anywhere in the cells), and return the corresponding value in Column
A.
(enter as Ctrl-Shift-Enter)

=INDEX(A2:A100, MATCH("*"&D1&"*"&"*"&D1&"*",B2:B100&C2:C100,0))


Don

 

[Frank Kabel]

Hi Doug
I sent you (at least i think I did) a working solution for your example
data?
Did you receive it?

 

[Wael]

Dear Frank,

I would very much appreciate if you send me the worked
example because i am having almost the same situation.

Thanks and regards,

Wael

 

[Myrna Larson]

You want the MID function, which will take out a piece of text given the
original text, the starting character, and the number of characters.

=IF(MID(B1,8,8)=MID(C1,14,8),A1,"no match")

Note that in your example you have 028849 and 00288849, so they wouldn't
match. I expect this is a typo.