# Search and reference

#### Ruzzo

##### Board Regular
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?

Ruzzo

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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?

Ruzzo

Try:
Excel Workbook
ABCD
1Apples and Orangesare sold at every store on the corner.900067Orange900067
Sheet1

Excel Workbook
ABCD
1Apples and Oranges are sold at every store on the corner.900067Orange900067
Sheet1

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

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.

Ruzzo

Replies
1
Views
196
Replies
1
Views
174
Replies
2
Views
358
Replies
2
Views
184
Replies
8
Views
493

1,207,199
Messages
6,077,023
Members
446,251
Latest member
dpf220

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back