I trying to do insert an image into a cell in google sheets. I'm using vlookup and regex extract to lookup columns to retrieve the image URL for the IMAGE function in google sheets.

However one of the lookup columns has the lookup value (SKU) but in a string.

When the string ends with the lookup value (SKU) it works, when it starts with the lookup value (SKU) it does'nt work.

I have shared the sheet with examples where it works and where it doesn't.

I'd like it to work regardless of where the lookup value is (SKU) in the string even it the string has special characters.

The vlookup search_Key is something like this

GK0095
GK0082
GK0083
GK0087
GK0086

The vlookup range uses regexextract to get the search_key from the first column of the range which has a string that contains the search_key.

For example

GK0095-gustav-klimt-margaret-stonborough-wittgenstein.jpg
gustav-klimt-apple-tree-GK0082.jpg
GK0083 gustav klimt poppy field.jpg
GK0087-gustav-klimt kirche in cassone.jpg
GK0086.jpg

So how do I use regexextract within vlookup to lookup the search_key from a no pattern string that contains the search_key.

I using this

=FILTER(VLOOKUP($A2,{REGEXEXTRACT(Sheet2!$A:$A,"([^.-]*)\."),Sheet2!$B:$B},2,0),$A2<>"")

But this only seems to work when the search_key is at the end of the string or if the string only contains the earch_key.

This was solved using the following at this thread

=image(iferror(vlookup(A2&"*",Sheet2!A:B,2,0),vlookup("*"&A2&"*",Sheet2!A:B,2,0)),1)

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.