Pricing + Additional costs

[DHFE]

Hi,

I have a sheet with 3 columns. Job, Work, Price. I have a 2nd sheet with 2 columns where one is a list of different job types and the other is their relevant prices.
Job - drop down list based off Sheet 2
Work - number
Price - number
What i want to do is create a formula where the price on the first sheet if automatically populated based on the job type and the number in the Work column.

So the second sheet contains, basically; (a vlookup called price)
*first line is blank
Job 1 - 50
Job 2 - 60
Job 3 - 70
Job 4 - 80
Job 5 - 90

What i need is if on the first sheet Job = Job 1 or Job 2 or Job 3, then just the price is displayed (the number in the work column has no factor)
And if Job (sheet 1) = Job 4 or Job 5, AND the number in the Work column is less than 5 then just the price is displayed
BUT if Job (sheet 1) = Job 4 or Job 5, and the number in the work column is 5 or more then an additional 12 is added to the price for each one over 4 (i.e. if it was 5 then 12 is added, but if it's 6 then 24 is added)

I had this, but it's not working and i can't think where i've gone wrong. Because parts of it work separately.
=IF(A2=OR(Sheet2!A2,Sheet2!A3,Sheet2!A4),VLOOKUP(A2,prices,2,FALSE),IF(AND(A2=OR(Sheet2!A5,Sheet2!A6),B2<5),VLOOKUP(A2,prices,2,FALSE),VLOOKUP(A2,prices,2,FALSE)+((B2-4)*12)))

Edit 1;
I think it's the OR part where there's a fault

Edit 2;
It was the or - needed to be before the A2=
=IF(OR(A2=Sheet2!A2,A2=Sheet2!A3,A2=Sheet2!A4),VLOOKUP(A2,prices,2,FALSE),IF(AND(OR(A2=Sheet2!A5,A2=Sheet2!A6),B2<5),VLOOKUP(A2,prices,2,FALSE),VLOOKUP(A2,prices,2,FALSE)+((B2-4)*12)))

Edit 3;
Ok that hasn't worked either - as it's removing the 12 when the value in Work is less than 5 (takes off 12 for 3, takes of 24 for 2, etc)

Edit 4;
Needed $
=IF(OR(A2=Sheet2!$A$2,A2=Sheet2!$A$3,A2=Sheet2!$A$4),VLOOKUP(A2,prices,2,FALSE),IF(AND(OR(A2=Sheet2!A5,A2=Sheet2!$A$6),B2<5),VLOOKUP(A2,prices,2,FALSE),VLOOKUP(A2,prices,2,FALSE)+((B2-4)*12)))

[martindwilson]

try a shorter
=VLOOKUP(A2,prices,2,FALSE)+IF(AND(B2>=5,OR(A2=Sheet2!$A$5,A2=Sheet2!$A$6)),(B2-4)*12,0)
ps your formula worked fine

[DHFE]

oh right yeah, i see what you've done. That's great cheers.