CODEWITHSUNDEEP
excel
Chris D'Aniello
Chris D'Aniello

Reputation: 1

How to return the value of a certain cell using other cells as variables?

I've built a pricing model in Excel that returns in cell E1 the average price across many vendors, pricing options, etc., for varying Products (defined in cell E4) and Quantities (defined in cell E2).

In creating cost sheets, however, I have to plug in each possible quantity into E2 and product into E4, then manually copy (or type) the returned average price from E1 into the table.

I'm thinking there must be a way for me to fill the table with what would be the value of E1 if E2 were equal to X and E4 were equal to Y, where X and Y are each the Quantity and Product defined in columns of the table.

For example, I have:

Column A | Col B. | Col. C | Col. D
 Universe | Qnty. | Product | Price
 Universe A | 1,000 | Prod 1 | ???
Universe B | 5,000 | Prod 2 | ???
Universe C | 3,000 | Prod 2 | ???

Essentially I want to turn cells E2 and E4 into variables and have a column on the table return "value of $E$1 when $E$2=B1 and $E$4=C1", rather than manually entering each quantity and product.

Upvotes: 0

Views: 123

Answers (2)

Dan Donoghue
Dan Donoghue

Reputation: 6206

What is the formula in E1? Just use that as part of the formulas in column D, for example if E1 is something like =B1 and E4 is something like =E2/Sum(B:B) and you want to add 1 to it make D something like 

=(B1/Sum(B:B))+1

Upvotes: 0

Mark
Mark

Reputation: 309

I didn't quite follow your logic. Can you put your model/calculations in a separate worksheet, then pull in the returned average price for each product back into the main sheet?

Upvotes: 1

Related Questions