Lookup part of a cell

[Doug]

I am familiar with lookups ,but i need the formula that
will lookup only part of a cell that corresponds with part
of the lookup cell.

Example

A B C D
1 FRED A123X ABC123XY

Formula required = lookup "123" in cell D1 and
return "FRED"(B1) if "123" is contained in in C1

Thanks for any help.

Doug

 

[Frank Kabel]

Hi
try the following array formula (entered with CTRL+SHIFT+ENTER):
=INDEX(B1:B100,MATCH(TRUE,ISNUMBER(FIND("123,C1:C100)),0))

 

[Doug]

I made an error in my original formula requirement.

I need to look up just the first 8 numbers only ignoring
letters in both cells D1 and C1.
There will always be 8 letters in both cells

Thanks

Doug

 

[Frank Kabel]

Hi
could you give an example. Not quite sure WHAT you want to lookup?

 

[Doug]

Frank,

The last post should have read
"there will always be 8 numbers in both cells"
What I have is 3 sets of data,in A1 is a name in B1 there
are letters and a unique 8 number code,(eg ABC12345678)
The leters can be more than 3 but the code will always be
8 numbers.
C1 is similar to B1 but the letters and numbers are
arranged differently,(eg 12345678fghijk).
The common factor is that the number in each cell B1 and
C1 is the same ,both consisting of the same 8 numbers in
the same sequence.
What i am trying to do is equate the number in C1 with the
number in B1,ignoring the letters, and return whatever is
in cell A1.

Hope that makes it a bit clearer.

Doug

 

[Frank Kabel]

Hi
and in C2 ist the number always at the beginning (the first 8
characters)?. I would try to separate this information in two different
cells though

 

[Doug]

Frank,
these are 2 actual examples of cells B1 and C1

B1: Order: 0028849||||||

C1 : Advice note: 00288849/001

All the other cells in columns B and C are in exactly the
same order.
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

-----Original Message-----
Hi
and in C2 ist the number always at the beginning (the first 8
characters)?. I would try to separate this information in two different
cells though

--
Regards
Frank Kabel
Frankfurt, Germany

Frank,

The last post should have read
"there will always be 8 numbers in both cells"
What I have is 3 sets of data,in A1 is a name in B1 there
are letters and a unique 8 number code,(eg ABC12345678)
The leters can be more than 3 but the code will always be
8 numbers.
C1 is similar to B1 but the letters and numbers are
arranged differently,(eg 12345678fghijk).
The common factor is that the number in each cell B1 and
C1 is the same ,both consisting of the same 8 numbers in
the same sequence.
What i am trying to do is equate the number in C1 with the
number in B1,ignoring the letters, and return whatever is
in cell A1.

Hope that makes it a bit clearer.

Doug