Does cell A contain cell C formula/macro help

[timohteee]

Hello,

I have a spreadsheet right now that contains a column for a unique identifier of a file. These strings can be over 380 characters long. I have another spreadsheet that has a master list of these unique IDs some of which contain a pipe delimiter because they have multiple values within one cell.

I'll try and explain with this simple table (my data is more complex)

Table A:

Column A	Column B
cat
dog
door

<tbody>
</tbody>

Table B:

Column C	Column D
cat|lizard|ant|hamster	small
dragon|hippo|dog|buffalo	big
stove|door|microwave|television	home

<tbody>
</tbody>

Basically what I want is to search for the string 'cat' from Column A in Column C and then pull back Column D into Column B. So the first cell in Column B would be return the value 'small'.

Right now, I have delimited Column C and I'm running multiple VLOOKUPs (one at a time) while also using the RIGHT formula because as I said, the characters exceed the amount accepted by VLOOKUP.

=VLOOKUP("*"&RIGHT(A1,240),'[Book1.xlsx]Sheet1'!$A:$B,2,0)

If I continue with this method of delimiting the cell and running VLOOKUPs one at a time, I'll have to do at least 40 more VLOOKUPs.

Any easier way to accomplish this?

 

[Firesword]

Hitimohteee
That won't work without a slight change to the data in Table B, you will need every word to be enclosed with the pip IE "cat|lizard|ant|hamster" will need to be "|cat|lizard|ant|hamster|", then this will work:-
=INDEX(Sheet1!$D:$D,MATCH("*|" &A2&"|*",Sheet1!C:C,0),1)

=index(sheet1!$D:$D This will be the range to return in table B Column D, matchMATCH("*|" &A2&"|*" A2 will be the lookup value in Table A column A &"|*",Sheet1!C:C,0),1) and Sheet1!C:C will be the list of names to search

 

[timohteee]

Thanks for your reply, Firesword. However, that doesn't fit my needs as I'm basically looking to search for the string within a cell and pull back the cell right next to it. I don't have all the values that will be within Column C from Table B nor the order they will be in.

 

[Firesword]

That formula will take the name in Table A, Column A, ie Cat and look for cat in any format in table B column C and then return the value in table B column D, is that what you were after? The reason I suggested the change is if you had Catfish in your string, and catfish was infront of cat then it would report the value of catfish and not cat. In your string all you'd need to do is make sure that you start with a | and end with a | for each item.