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

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

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.

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

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

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

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.

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

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

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

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),"")

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

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.

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

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.

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

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

... 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

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

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")

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

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.

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

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

Cheers!

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

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))

There are currently 1 users browsing this thread. (0 members and 1 guests)