VLOOKUP within VLOOKUP

[VivienW]

Hello, I'm using VLOOKUP tables and have a problem entering a
formula/function. For example, the data I'm using concerns engine capacity
and fuel types; this data is laid out as follows

Capacity Petrol Diesel (This table is named 'PETROL2 and starts in cell A3)
0 £0.30 £0.20
1100 £0.40 £0.30
1300 £0.42 £0.32
1600 £0.45 £0.35
2000 £0.47 £0.37

Miles Capacity Type Cost (starts in B12)
10 1100 P £0.40 (Formula:
=IF(D12="P",VLOOKUP(C12,PETROL2,2),IF(D12="D",VLOOKUP(D12,PETROL2,3)))
200 1297 P £0.40
300 1100 D #N/A
50 2000 P £0.47
100 1600 D #N/A

What I need is a formula that will return the total cost of the Diesel
entries. As you can see the Petrol entries are there! Please help, I really
can't see where I'm going wrong.

 

[Sheeloo]

I think the mistake is in using D12 as the lookup value in the second one...
Try
=IF(D12="P",VLOOKUP(C12,PETROL2,2),IF(D12="D",VLOOKUP(C12,PETROL2,3)))

i.e. both VLOOKUP should have VLOOKUP(C12,PETROL2, followed by either 2 or
3...

 

[VivienW]

Thank you so much!! I can't tell you how long I've been playing around with
this.

 

[Sheeloo]

You are most welcome.

This happens to all of us... I spent one hour today and finally traced the
error to an extra Carriage Return (in MS Word)