Is there a way to use ArrayFormula or Simplify this VLOOKUP?-CodePudding

Home > Software design > Is there a way to use ArrayFormula or Simplify this VLOOKUP?

Is there a way to use ArrayFormula or Simplify this VLOOKUP?

Time：11-11

=VLOOKUP(CONCATENATE(TEXT(G$1,"yyyymmdd"),OFFSET($A3,-$E3,0)),IMPORTRANGE(Settings!$B$2,"Main!$A:$AC"),14 $E3,false)

This is a huge sheet and the vlookups are taking awhile to process, so I am try to simplify and speed up the process.

CodePudding user response：

delete all vlookup formulae and try:

=INDEX(IFNA(TRANSPOSE(VLOOKUP(TEXT(FLATTEN(G1:M1), "yyyymmdd")&$A3, 
 IMPORTRANGE(Settings!$B$2, "Main!$A:$AC"), {14,15,16,17,18,19,20}, ))))