VLOOKUP with an IF function in one cell of the table

[TJW3428]

Hi - New member and novice excel user

I'm trying to set up a sheet that will calculate the price of a service based on variable pricing where the rate for the first three units is fixed at $59.30 whether it be 1,2,or 3 units and every unit after that is an extra $19.90.

I am using the formula =IF(A1>3,(A1-3)*19.9+59.3,59.3) to calculate the price which seems to work OK but I have ran into trouble when I try to incorporate it into a VLOOKUP table along with a set of other prices for other products and services.

I'm using two VLOOK up functions set up so the second list option is dependent on the first one

=IFERROR(VLOOKUP(D13,Table30[[Column1]:[Ref]],2,FALSE),"")

=IFERROR(VLOOKUP(E13,Table30[[Ref]:[Amount]],2,FALSE),"")

Is there a way that I can incorporate the IF function into the VLOOKUP table so it will automatically enter the right price according to the number of units I input? I have tried a number of formula's but its beyond my very basic skills.

Thanks for any ideas. - James

 

[mrshl9898]

So this is in E13?

IFERROR(VLOOKUP(E13,Table30[[Ref]:[Amount]],2,FALSE),"")

And 59.3 is the value you want to change with the VLOOKUP?

You can try this:

=IF(A1>3,(A1-3)*19.9+VLOOKUP(E13,Table30[[Ref]:[Amount]],2,FALSE),VLOOKUP(E13,Table30[[Ref]:[Amount]],2,FALSE))

Or can substitute E13 with the first lookup so you don't need it.

=IF(A1>3,(A1-3)*19.9+VLOOKUP(VLOOKUP(D13,Table30[[Column1]:[Ref]],2,FALSE),Table30[[Ref]:[Amount]],2,FALSE),VLOOKUP(VLOOKUP(D13,Table30[[Column1]:[Ref]],2,FALSE),Table30[[Ref]:[Amount]],2,FALSE))