What is wrong with the following equation?

Copper Contributor

I can't get the following formula to work




4 Replies

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


    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.



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

  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