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:
<tbody>
</tbody>
Table B:
<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.
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?
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.
Code:
=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?
Last edited: