Formula to identify part of a text in cell from range of cells & insert adjacent cell text

**Novicebutnotforlong** · 11-16-2013, 05:57 AM

Hi I'm trying to create a formula that would identify part of some text in a cell from a range of cells (column) in another spreadsheet and once it identifies the text insert the text from the adjacent cell.

I thought the formula =LOOKUP(A3,'Sheet1'!$B3:$B100,$C3:$C100)

Where A3 contains the text and the cells within the range B3:B100 will contain part of the text in A3 and the text in the cell adjacent to the cell containing the text of interest (range C3:C100) will then appear in the cell I put the formula in.

Hope this makes sense.

**XOR LX** · 11-16-2013, 06:05 AM

Hi and welcome to the forum!

Are you able to upload a small sample workbook with a few examples and your desired result in each case?

Regards

**Novicebutnotforlong** · 11-16-2013, 06:22 AM

Thanks for the welcome!
I dont seem to be able to upload a sample workbook, but this is what I'm hoping to achieve

Sheet1
Row A B C D
1 cat food
2 dog water
3 goat grass
4 elephant peanut

Sheet 2
A B C D
1 cat 722comp =LOOKUP(A1,Sheet1!A1:A4,B1:B4)
2 dog 643 comp
3 sheep 487 comp

Ok so what i want to do is create a forumla that will use the information in the cell sheet 2 A1 (cat 722comp) to identify part of that text in the range; sheet 1 A1:A4 (cat) and then paste in sheet 2 B1 the text in the adjacent cell Sheet 1 B1 (food). The idea then would be to repeat the formula for the subsequent cells in sheet 2 column A.

Hope that makes sense.

**XOR LX** · 11-16-2013, 06:26 AM

Sorry - not sure I understand. The two tables appear to be identical (?). Also, you've only put one example in there, so it's very difficult to determine exactly what you want.

What difficulties did you have in trying to upload the attachment?

Regards

**benishiryo** · 11-16-2013, 06:48 AM

welcome to the forum. what if the data in sheet2 is "doggie"? does it return "water" too or must a single word be "dog"? for the former, try:
=IFERROR(LOOKUP(2^15,SEARCH(Sheet1!$A$1:$A$4,A1),Sheet1!$B$1:$B$4),"")

for the latter:
=IFERROR(LOOKUP(2^15,SEARCH(" "&Sheet1!$A$1:$A$4&" "," "&A1&" "),Sheet1!$B$1:$B$4),"")

**Novicebutnotforlong** · 11-16-2013, 07:32 AM

Example Formula.xlsx

I worked out how to attach the spreadsheet. I would like sheet 2 to display the description by searching the accession numbers from column A in sheet 1.

**Novicebutnotforlong** · 11-16-2013, 06:30 PM

Example Formula with suggested formula.xlsx

Hi again,

I've uploaded the spreadsheet with the suggested formula. Any hints as to why it isn't working will be greatly appreciated.

**Max, Singapore** · 11-16-2013, 08:34 PM

My thoughts would be to approach it along these lines ...

Reference your file attached in #7 and your explanation in #6:
... I would like sheet 2 to display the description by searching the accession numbers from column A in sheet 1 (via matching with col D in Sheet2)

In Sheet2,
In N2: =TRIM(MID(D2,SEARCH("_",D2)+1,99))
Copy down. This strips off the substring to conform with what's in Sheet1 col A

Then in O2: =INDEX(Sheet1!B:B,MATCH(N2,Sheet1!A:A,0))
Copy down
---------------------
How does it go for you? Wave it, hit the little star at the bottom left of my responses

**benishiryo** · 11-16-2013, 10:29 PM

in your scenario, you should be using my 1st formula & not the 2nd one right? and you noticed i used dollar signs to lock my formula? that is to fix the range
=IFERROR(LOOKUP(2^15,SEARCH(Sheet1!$A$3:$A$11,'Sheet 2'!D2),Sheet1!$B$3:$B$11),"")

or if you want to put in "No hit", then:
=IFERROR(LOOKUP(2^15,SEARCH(Sheet1!$A$3:$A$11,'Sheet 2'!D2),Sheet1!$B$3:$B$11),"No hit")

**Novicebutnotforlong** · 11-17-2013, 02:50 AM

Thanks!
That worked brilliantly. I was just thinking is there a way to add to the formula to choose different sheets to search for data depending on what is written in the cell? For example if the cell in sheet 2 column D reads cow, then I would like the formula to search in sheet 1. However, if the cell in column D reads Cat I would like the formula to search in sheet 3.

**Novicebutnotforlong** · 11-17-2013, 02:57 AM

Thanks for the suggestion the formula in O2 didn't seem to work & I was hoping to use one formula.

Cheers!

**Max, Singapore** · 11-19-2013, 02:11 AM

Reply to Op's post #11
FWIW, it worked fine in my testing here. And you could collapse the formula in col N into 1 col, eg:
in O2: =INDEX(Sheet1!B:B,MATCH(TRIM(MID(D2,SEARCH("_",D2)+1,99)),Sheet1!A:A,0))

Formula to identify part of a text in cell from range of cells & insert adjacent cell text

LinkBack

Thread Tools

Rate This Thread

Display

Formula to identify part of a text in cell from range of cells & insert adjacent cell text

Re: Formula to identify part of a text in cell from range of cells & insert adjacent cell

Re: Formula to identify part of a text in cell from range of cells & insert adjacent cell

Re: Formula to identify part of a text in cell from range of cells & insert adjacent cell

Re: Formula to identify part of a text in cell from range of cells & insert adjacent cell

Re: Formula to identify part of a text in cell from range of cells & insert adjacent cell

Re: Formula to identify part of a text in cell from range of cells & insert adjacent cell

Re: Formula to identify part of a text in cell from range of cells & insert adjacent cell

Re: Formula to identify part of a text in cell from range of cells & insert adjacent cell

Re: Formula to identify part of a text in cell from range of cells & insert adjacent cell

Re: Formula to identify part of a text in cell from range of cells & insert adjacent cell

Re: Formula to identify part of a text in cell from range of cells & insert adjacent cell

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Splitting text from one cell into separate text fragments, Located in adjacent cells

Need to find text in a named range, and return part of the text to a single cell

[SOLVED] Find empty cells in a range and insert formula in adjacent cell

Insert cell with text within cell that has large amount of text(non formula)

Insert cell text as part of formula?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions