Formula with Variable Input

[ErikKAnderson]

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 of the formula in real time. For example: if A1 = 1 from dataset 1 and A2 = 2 for dataset 2, if I had a column of equations, I could toggle between y=A1x and y=A2x in the same column by selecting the called variables in some sort of drop down menu box. Is this or some variation possible?

[#2]

@ErikKAnderson 

I'm quite sure it's possible, but it's hard to be more specific without seeing the spreadsheet (or a mockup of it) itself.

If you can't post directly to this forum, you could grant access to a copy of your file on OneDrive or in GoogleDrive (or the equivalent).

In the absence of that, let me refer you to the LET function for starters. It could be used to enable the kind of flexibility in using data from different data sets based on inputs such as you describe.

[#3]

@ErikKAnderson 

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