Using Query, Index and Split within an arrayformula not working

1
votes

I have a sheet that has a column (D) with values separated by a "-", I'm trying to split the value and get the second half and use that in a query to look up and fetch another value.

For example Sheet 1

Col D         |
--------------|
67009-XYZ0001 |

Sheet 2

Col C       | Col D | Col E    |
------------|-------|----------|
XYZ0001.jpg |       | test.com |

The formula should result in "test.com"

It works when used by it's self in a cell.

=query(Info!A:F,"Select E where C contains '"& index(SPLIT(D2,"-"),0,2) &"'")

Using it in an array formula without query the split and index works fine.

=ArrayFormula(IF(ROW(A:A)=1,"GET URL,index(SPLIT(D:D,"-"),0,2)))

But when I try using it in an array formula along with query it doesn't work.

=ArrayFormula(IF(ROW(A:A)=1,"GET URL",query(Info!A:F,"Select E where C contains '"& index(SPLIT(D:D,"-"),0,2) &"'")))

Is there any other way of achieving this.

Any help is appreciated. Thanks

I have shared the link to the spreadsheet

  • Sheet 1 shows the wrong result

  • Sheet 3 shows the desired result, but only works in cell.

1
google-sheets
Can you share a copy of your spreadsheet? - JPV
For future reference: Array Formula doesn't work with Query (and couple of different array based formulas) - Grzegorz Mogilewski
Have shared a copy of the spreadsheet. - user2240778

1 Answers

1
votes

=FILTER(VLOOKUP(query(SPLIT(D2:D,"-"),"select Col2"), {REGEXEXTRACT(Sheet2!C:C,"[^.]*"),Sheet2!E:E},2,),D2:D<>"")

  • query: get an index of column 2, function index does not work with arrayformula
  • vlookup: get exact match
  • regexExtract: get the part of text before dot (.).

Note:

  • query text "contains" does not work with multiple entries, it is not equivalent to statement "in"