How to use Vlookup and IF function together in a table. Is there an alternative method?

[shniblee14]

I'm trying to make a table that determines the price of a track depending on the length of the track and the track type.



What I want is to have a table that does the following:



Column A= Track Type (Ceiling fix and Face fix)

Column B = Track Length

Column C = Track Price



If track type = ceiling fix and track length is between 0-1000 then price is $32.

If track type = ceiling fix and track length is between 1000-1500 then price is $38.

If track type = face fix and track length is between 0-1000 then price is $28.

If track type = face fix and track length is between 1000-1500 then price is $34.


I'm using excel 2016.

So far I have only been able to apply a dependent drop down list to the track type and applied the vlookup function to the price column which returns only prices from the 0-1000 range for each track type.

I have used the IF function in the past to work out the price based on track length but I'm not sure how to apply it to work with the vlookup function.


Any help would be appreciated!



Thank you

 

[mrhstn]

If these are the only options then you could try something like

=28+LOOKUP(A2, {"ceiling fix",4; "face fix",0})+(6*(B2>1000))

Assuming A2 holds the track type and B2 holds the track length.

The LOOKUP might be overkill...

 

[shniblee14]

If these are the only options then you could try something like

=28+LOOKUP(A2,{"ceiling fix",4;"face fix",0})+(6*(B2>1000))

Assuming A2 holds the track type and B2 holds the track length.

The LOOKUP might be overkill...

Thank you, I will try this and see how it goes.

Ideally, if this works, there will be around 5-6 different track type options each with their own set of pricing for each length range.