search text string within array

[Geeban]

Hello,

I am trying to look for partial text within a cell. What I am trying to do
is to find a cell that contains specific text from a cell. I am working from
2 different sheets in the same workbook.
Sheet 1, row A contains the container number I am looking for. Sheet 2 row A
also contains the container numbers, although some of these have 2 container
numbers in the same cell. Sheet 2, row C contains ETA which is what I would
like to return.
As some of the cells contain 2 container numbers, I cannot use a vlookup
find either of them. To put it simply, I want to use a lookup from partial
text and return a value on a corresponding row.

Any help is greatly appreciated.

 

[T. Valko]

Sheet 1, row A contains the container number I am looking for

Post several representative samples of these.

Sheet 2 row A also contains the container numbers,
although some of these have 2 container numbers
in the same cell.

Post several representative samples of these, also.

 

[Geeban]

Hi,

A B C
D
Container ETA Container
ETA
MSKU APLU
01/11
APLU MSKU CMAU
01/12
CMAU ECMU
01/01
ECMU

To make this a bit less confusing, I will use the above layout on only 1
sheet. Essentially I would like to do a vlookup in column B:

=vlookup($A2,D:E,2,false)
This would return:
B2 = #N/A
B3 = 01/11
B4 = #N/A
B5 = 01/01

This returns an error for MSKU and CMAU as their cell value is not identical
to C3.
How can I lookup a text string in a cell, even if it is not exact but does
contain the text. I would like B2 and B4 to return 01/12.

Any help is greatly appreciated.

 

[Max]

=vlookup($A2,D:E,2,false)

Try in B2, copied down: =VLOOKUP("*"&$A2&"*",D:E,2,0)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,000 Files:359 Subscribers:56
xdemechanik
---

 

[Geeban]

YES!!!!This works. So simple but thanks a lot for your help.

 

[Max]

Welcome. Do take a moment to rate responses which help by pressing the "Yes"
button (like the ones below), from where you're reading this.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,000 Files:359 Subscribers:56
xdemechanik

 

[ginal patel]

Hi,

I have a similar question, with a slight twist:

How do I look up part of a value from a cell and return the value I'm looking up? Here is an example below:

Here is the lookup table:

C D
blue Dresses / Blue Dresses
lace Dresses / Lace Dresses
sequin Dresses / Sequin Dresses
party Dresses / Party Dresses
petite Dresses / Petite Dresses


And here is my list of terms I need to categorise:

A
karen millen dress blue
karen millen dress's blue
karen millen lace dress
karen millen lace dress's
karen millen sequin dress
karen millen sequin dress's
debenhams party dresses
oasis sequin dress
oasis sequin dress's
cos dress
cos dress's
precis petite dresses
h&m dress's red

for column A, part of the cell can be found in the lookup table. I want to search column A for the column C, and return column D. Is this possible?

 

[george wilson]

use the following

=LOOKUP(2^15,SEARCH(C$6:C$14,$A3),$D$6:$D$14)

the search will return the ordinal position in the array in col c and the lookup will return the value you want from col d. N/A can be reduced by taking out the spaces in col a.