Ceiling and Vlookup

[tomdss]

Hello all,

I have finally given up and decided to ask for some help please...

Where I have come unstuck is trying to always 'round up to the nearest 500' (never down) within a VLOOKUP calc.

Currently I have a work around as follows (Cell X3);
=VLOOKUP(E3,'Mat & Finish £ Guide'!G:H,2,FALSE)*AC3/1000

G - Material Type
H - £ per linear meter

Cell AC3;
=CEILING(I3,500)

Is there a way of incorporating the calculation in AC3 into Cell X3?

Thank you for any time and assistance.

Tom

[Pete_UK]

You can just substitute the reference to AC3 with the formula, like this:

=VLOOKUP(E3,'Mat & Finish £ Guide'!G:H,2,FALSE)*CEILING(I3,500)/1000

Hope this helps.

Pete

[JohnTopley]

This ..

=VLOOKUP(E3,'Mat & Finish £ Guide'!G:H,2,FALSE)*(CEILING(I3,500)/1000

but I don't think this is what you want.

Post a small sample Excel file showing expected results you want.

Click "Go advanced" then "Paper Clip" icon to upload a file.

[tomdss]

Pete and John,

Thank you so much for your help. Problem solved and now my hair can begin to grow back.