How do I create a formula based on a drop down list choice

Copper Contributor

Good Afternoon,

I am trying to set up a new spreadsheet which minimises the workload required each time info is inputted. 

Is it possible for a cell formula to change based on the choice from a drop down list?

In the example attached, to work out the gross weight of material the formula is (Gauge x Width x Pitch) x Gross usage calc. The gross usage calc changes depending on the material. 


If you are to change the drop down choice in B7 can this change the formula needed in D2?


Excel Material example.png


Drop Down List Formula.xlsx



4 Replies









Hi @Hans_Vogelaar

Thank you for your response. That has worked!

Have a great day
Hi @Hans_Vogelaar

Thank you for your help on both of my posts.

I am having an issue with the XLOOKUP formula used above. Reaosn being I have excel 2021 whereas my colleague currently has excel 2016 which i believe XLookup doesnt work in.
Is there a formula which would work for both 2016 and 2021?


Use VLOOKUP instead of XLOOKUP: