Lookup value problem in excel

[Nogi]

Hi all,

I have a question regarding Vlookup, or maybe another feature i could us to search for a value in a cellrange.

Problem:
--------
In sheet 2 column A: i have cells containing text.
Ex. A1: Not pickled E10 instant devorced
A2: Foreseen in B30 always there
A3: ...

B1: P1114
B2: P1123


In sheet 1 column A: i have for example in
A1: E10
A2: B30
A3: ...

I am searching for a way to get the values of Sheet 2 column B, in sheet 1 column B.

Problem is, that if i have in sheet 1, column A1, lookup value is E10, it is not found in the table A on sheet 2, since E10 is used in a sentence, and not as single value in a cell.

If on sheet 2 column A cell 1, value is E10, it is found.
But in the sentence "Not pickled E10 instant devorced", the E10 is not found, and therefore there's a "#N/A" error in column B.

In short: is there a way to do a lookup in excel, not based on a single cell value, but on a specific word of the cell value?

 

[SkipVought]

Hi,

Are you stating by your example, that since E10 is in A1 on sheet 1 that you expect to find (or not) "E10" embedded in A1 on sheet2 or ANYWHERE in column A on sheet 2?

What do you want returned in column B on sheet1--if the value in Sheet1!A1 is found embedded in Sheet2!column A, return the value in column B?

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[Nogi]

Well, the idea is that sheet 1, cell A1 is E10, and that in sheet 2 column A SOME cell has E10 embedded in it.
Sheet 1, cell A2 is B20, and SOME cell in sheet 2, column A has B20 embedded in it.

So if it would be in A10, it should return value B10.
If it were A123, it would return B123 and so on.

 

[xlbo]

Please search before asking questions - this thread was answered recently:

Lookup part of a cell thread68-1225074

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question