Can't display image in G sheets cell using Vlookup to get the link (returns blank or error)

user_8193912 0
Viewed 338

I have a Google sheet with answers to a form, and I created a template in Google Sheets to transform each row in the answers sheet in a report, using vlookup (=PROCV in portuguese). The last column in the spreadsheet is a link for an image, uploaded by who answered the form.

The vlookup is working fine, except for the image. It's not displaying the image in the cell.

I have tried:

=image(PROCV(H7;'Respostas ao formulário 1'!1:630;21))

It returns a blank cell.

and also

=IMAGE("https://drive.google.com/uc?export=download&id="&INDEX(SPLIT(PROCV(H7;'Respostas ao formulário 1'!1:630;21),"="),1,2))

and

=IMAGE("https://drive.google.com/uc?export=download&id="&REGEXEXTRACT(PROCV(H7;'Respostas ao formulário 1'!1:630;21),"id\=(.+)"))

which I found here in stackoverflow, in another question about this issue, and return an "ERROR".

Also important, I've already changed permissions in the image (anyone with the link can access).

Any insights, please? Thank you in advance for your time and attention.

vlookup google-sheets image
2 Answers

Try setting the "is_sorted" parameter to false. From your first formula I gather you are looking up the value in H7 against the sheet 'Respostas ao formulário 1' rows 1 through 630 and you want the 23 column (W). Some areas that can give you problems is setting the sort to false, so it has to be an exact match, is the lookup value in the first column of the resource sheet?

Essentially with a format matching this you shouldn't have any issues:

Sheet named 'Respostas ao formulário 1'. Notice I have hidden columns B:V to consolidate the image and only show important information.

enter image description here

Finally the formula sheet:

We are looking up the value in cell F1 "Value1" against the second sheet and returning the image from the link.

enter image description here

user_75020290

If http://drive.google.com/uc?export=download&id={file_id} does not work, try this format:

Link Format:

http://drive.google.com/uc?export=view&id={file_id}

Try this publicly available image: http://drive.google.com/uc?export=view&id=1YEpZitKgY6YT0TMDvOX4uqoxD5_aTuEv

Raw link image formula;

=image("https://drive.google.com/uc?export=view&id=1YEpZitKgY6YT0TMDvOX4uqoxD5_aTuEv")

Formula using regexextract to get file ID:

=Image("http://drive.google.com/uc?export=view&id="&REGEXEXTRACT(VLOOKUP(H7,'Respostas ao formulário 1'!1:630,21),"id\=(.+)"))

Outputs:

  • Vlookup output: http://drive.google.com/uc?export=view&id=1YEpZitKgY6YT0TMDvOX4uqoxD5_aTuEv

  • Regexextract output: 1YEpZitKgY6YT0TMDvOX4uqoxD5_aTuEv

  • Raw link image output: output2

  • Image regexextract output: output

Note:

  • If the image is still not showing, there is an issue with your link and it may not be available to anyone else. To confirm this issue, open the link in incognito mode. If it shows, it is publicly available. If not, then it isn't and the image will not show using IMAGE formula.
  • Make sure the one you append to the link format is only the ID and nothing else.
  • See sample sheet

EDIT:

  • It seems your lookup ID contains #, add a backslash before it by using the below formula

Formula:

=Image("http://drive.google.com/uc?export=view&id="&REGEXEXTRACT(VLOOKUP(regexreplace(H7; "#"; "\#");'Respostas ao formulário 1'!1:630;21);"id\=(.+)"))
user_161186860
Related