Search code examples
imagegoogle-sheetsgs-vlookup

lookup country name and return flag image to cell in Google Sheets


I have a country list of 245 countries.

Is there any way I can use a VLOOKUP in Google Sheets to import their respective flags?

I was thinking of potentially using a resource such as Wiki or http://www.theodora.com/flags/ but not sure if I can?


Solution

  • Step 1. Get links

    A1 = http://www.sciencekids.co.nz/pictures/flags.html

    B1 = //@src[contains(.,'flags96')]

    A3 = =IMPORTXML(A1,B1)

    Step2. Use image function

    B3 = =IMAGE(substitute(A3,"..","http://www.sciencekids.co.nz"))

    Bonus. Country name:

    C1 = ([^/.]+)\.jpg$

    C3 = =REGEXEXTRACT(A3,C1)

    enter image description here