Search and reference

[Ruzzo]

Cell A1 has a bunch of reference words in each cell.
B1 is blank
Cell C1 has just a simple word
D1 has numbers associated with C1.

For cell B1-
IF a sequence of letters or words contained in C1, is referenced in cell A1 in any place, I want to return the value of D1, into B1.

i.e. –
A1- Apples and Orangesare sold at every store on the corner.
B1- blank
C1- Orange
D1- 900067

I want cell B1 to return 900067, because the word Orange appears in A1.

Can someone provide the formula for cell B1?

Thanks in advance.
Ruzzo

 

[Robert Mika]

Cell A1 has a bunch of reference words in each cell.
B1 is blank
Cell C1 has just a simple word
D1 has numbers associated with C1.

For cell B1-
IF a sequence of letters or words contained in C1, is referenced in cell A1 in any place, I want to return the value of D1, into B1.

i.e. –
A1- Apples and Orangesare sold at every store on the corner.
B1- blank
C1- Orange
D1- 900067

I want cell B1 to return 900067, because the word Orange appears in A1.

Can someone provide the formula for cell B1?

Thanks in advance.
Ruzzo

Try:

Excel Workbook
	A	B	C	D
1	Apples and Orangesare sold at every store on the corner.	900067	Orange	900067
Sheet1

 

[njimack]

Excel Workbook
	A	B	C	D
1	Apples and Oranges are sold at every store on the corner.	900067	Orange	900067
Sheet1

 

[Ruzzo]

Thanks. This almost works. My reference (A column) is kind of tricky. It may say Apples and Orang (only). Without the e.
So, how would I tell it to reference part or all of what is in the 'C' cell.

Thanks
Ruzzo

 

[Ruzzo]

Ok, here's the better question.
How would I reference the first 10 characters of cell C, instead of trying to reference the whole word. (I know this example has only 6).
Most of the time, cell A will not contain all of what would be in cell C.

Thanks in advance.
Ruzzo