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

[ElliotDT]

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?

Drop Down List Formula.xlsx

Cheers

[#2]

@ElliotDT 

Use

=PRODUCT(A2:C2,XLOOKUP(B7,$F$2:$F$4,$G$2:$G$4))

or

=A2*B2*C2*XLOOKUP(B7,$F$2:$F$4,$G$2:$G$4)

[#3]

Hi @Hans_Vogelaar

Thank you for your response. That has worked!

Have a great day

[#4]

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?
Cheers

[#5]

@ElliotDT 

Use VLOOKUP instead of XLOOKUP:

=A2*B2*C2*VLOOKUP(B7,$F$2:$G$4,2,FALSE)