Forum Discussion
Formula with Variable Input
I am trying to write a formula that calls coefficients determined from different model datasets. Ideally, I would be able to toggle between the different model coefficients to see the updated results...
The easiest way of achieving this is to collect the model coefficients together as the column of a Table with the model name as the header. Use the header row as the validation list for the selected model and XLOOKUP to return the data.
= XLOOKUP(selected,models,coefs)If the coefficients are on different sheets as a 3D range, Excel 365 beta can built the lookup tables from the range reference
= XLOOKUP(selected,
HSTACK(header),
HSTACK(content))
where header
= Sheet1:Sheet3!$C$3
and content
= Sheet1:Sheet3!$C$4:$C$40