# What is wrong with the following equation?

Copper Contributor

# What is wrong with the following equation?

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

# Re: What is wrong with the following equation?

@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.

# Re: What is wrong with the following equation?

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...)

# Re: What is wrong with the following equation?

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

Thanks Lz