google spreadsheets - hlookup applied to new rows using arrayformula

1
votes

I have a spreadsheet to which new rows are automatically added from an outside source. I'm using hlookup to find where in the row (which column) the price is located (all the prices contain a € sign)

=IF(B2="","",HLOOKUP("*"&"€"&"*",offers!K2:2,1,0))

I can just copy the formula by dragging it down, but this way it will not be automatically applied to the new rows. This is why I tried using ARRAYFORMULA, but I can't get it to work.

How can I combine ARRAYFORMULA AND HLOOKUP (or an alternative) so that formulas are applied to all new rows as well?

spreadsheet simplified example

1
google-sheets
@player0 I added a link to a simplified version.Additional rows are created via an external script. – dean2020

1 Answers

0
votes

paste this in A1:

={""; ARRAYFORMULA(TRANSPOSE(SPLIT(CONCATENATE(IF(LEN(C2:C), 
  IF(REGEXMATCH(D2:G, "€"), D2:G&";", ), )), ";", 1, 1)))}

1

alternatives:

=ARRAYFORMULA(IF(D2:D="", , VLOOKUP(ROW(A2:A), {ROW(A2:A), C2:H}, MMULT(COLUMN(B:G)*
 ISNUMBER(FIND("€", C2:H)), TRANSPOSE(COLUMN(C:H)^0)), 0)))

=ARRAYFORMULA(IF(D2:D="", , MMULT(IFERROR(1*SUBSTITUTE(SUBSTITUTE(C2:H, "€", ""),
 ",", "."), 0)*ISNUMBER(FIND("€", C2:H)), TRANSPOSE(COLUMN(C:H)^0))))