What is wrong with the following equation?

Copper Contributor

I can't get the following formula to work

 

 

=FORECAST.LINEAR(L20,{XLOOKUP(L20,B26:B30,B26:B30,,-1),XLOOKUP(L20,B26:B30,B26:B30,,1)},{XLOOKUP(L20,B26:B30,D26:D30,,-1),XLOOKUP(L20,B26:B30,D26:D30,,1)})

4 Replies

@John_Bloggsajohnso9 You cannot place formulas inside of curly brackets { } to generate an array of results. Use either HSTACK or VSTACK instead:

 

=FORECAST.LINEAR(L20,
    HSTACK(XLOOKUP(L20,B26:B30,B26:B30,,-1), XLOOKUP(L20,B26:B30,B26:B30,,1)),
    HSTACK(XLOOKUP(L20,B26:B30,D26:D30,,-1), XLOOKUP(L20,B26:B30,D26:D30,,1))
)

 

If HSTACK and VSTACK are not available in your version of Excel, output the results of each XLOOKUP formula to their own cells and reference the corresponding ranges for the known_ys and known_xs results.

@John_Bloggsajohnso9 

 

If HSTACK is not available in your version of Excel you can simulate it as follow:

=FORECAST.LINEAR(L20,
  CHOOSE({1,2}, XLOOKUP(L20,B26:B30,B26:B30,,-1), XLOOKUP(L20,B26:B30,B26:B30,,1)),
  CHOOSE({1,2}, XLOOKUP(L20,B26:B30,D26:D30,,-1), XLOOKUP(L20,B26:B30,D26:D30,,1))
)

(not tested but should work...)

Thanks DJ Clements. I thought it might have something to do with the curly brackets.
Thanks Lz