Home > front end >  Why is this ARRAYFORMULA VLOOKUP() REGEXEXTRACT() with multiple conditions not working?
Why is this ARRAYFORMULA VLOOKUP() REGEXEXTRACT() with multiple conditions not working?

Time:11-30

In a sheet, I have public images whose links are like this https://drive.google.com/file/d/1-gnl6ODS5sinOPsd0fcvszbA-bduNE2I/view?usp=sharing and the result should be: https://drive.google.com/uc?export=view&id=1-gnl6ODS5sinOPsd0fcvszbA-bduNE2I.

So, I'm working on this formula to have it the expected yield: https://drive.google.com/uc?export=view&id=1-gnl6ODS5sinOPsd0fcvszbA-bduNE2I

The formula:

=ARRAYFORMULA(IF(A3:A="",,Image("https://drive.google.com/uc?export=view&id="&regexextract(VLOOKUP($J$3:$J&" "&$N1:$N$1,
 QUERY({
 IMPORTRANGE("1gh5w0czg2JuoA3i5wPu8_eOpC4Q4TXIRhmUrg53nKMU","Image Link!A1:A")&" "&
 IMPORTRANGE("1gh5w0czg2JuoA3i5wPu8_eOpC4Q4TXIRhmUrg53nKMU","Image Link!C1:C"),
 IMPORTRANGE("1gh5w0czg2JuoA3i5wPu8_eOpC4Q4TXIRhmUrg53nKMU","Image Link!F1:F")}, 
 "select Col1,Col3"), 2, 0)),"d/(. )/view")))

This is the enter image description here

  • Related