arraysgoogle-sheetsgoogle-sheets-formulaarray-formulasgs-vlookup

Refer range from a cell value

I have the code below and I want D6:D and F6:F to be in a cell. Cell M83 has D6:D and cell M80 has F6:F.

QUESTION: HOW CAN I USE INDIRECT FOR 'Sheet2'!D6:D & 'Sheet2'!F6:F*1

={"Messages Sent"; ARRAYFORMULA(IFNA(VLOOKUP(INDIRECT(K80), 
 {REGEXEXTRACT(""&'Sheet2'!D6:D, TEXTJOIN("|", 1, INDIRECT(K80))), 'Sheet2'!F6:F*1}, 2, 0)))}
Solution

  • try:

    ={"Messages Sent"; ARRAYFORMULA(IFNA(VLOOKUP(INDIRECT(K80), 
 {REGEXEXTRACT(""&INDIRECT("Sheet2!"&M83), TEXTJOIN("|", 1, INDIRECT(K80))), 
  INDIRECT("Sheet2!"&M80)*1}, 2, 0)))}