Vlookup from multiple tables rounding to the closest number with unequal increments

[Ryan123]

Hi Guys

Im trying to simulate fuel use for a number of generators and need a formula that can do the above. I have a formula for one table returning a closest number with unequal increments and have a Vlookup to return from multiple tables however only returns the next lowest value. I cant seem to join these two formulas together to give me what i need. Can anyone help?

Cheers
Ryan

[Kaper]

Sure, but at this point it is just guessing.

You can find next lowest and next highest value, compare these with real value using ABS and select closer one.

Ith the above is not enough, then please follow http://www.excelforum.com/forum-rule...rum-rules.html advice, and attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Ryan123]

Thanks Kaper

I'm still getting used to the site so thanks for your help, i (think) i have attached a file that i am using. On the sheet labeled
'load input GS Day; you will find a table for January. In teh columns labeled ' fuel consumption you will find a formula that returns from
a single table a number rounded to the closest number with unequal increments. In the 'J24' you will find a formula that will select a number
from multiple tables and return a figure from column 2 however it rounds down instead of to the closest. I cant seem to get these two formulas
to work together. Would be great if you could take a look and see if you can get it to work.

Thanks and regards
Ryan

[Kaper]

The layout of worksheet part with your "small tables" is not very convenient, but as it is reasonably regular, table selection can be done a bit easier/shorter way:

Please Login or Register  to view this content.

but this of cours returns exactly the same what "old formula"

Please Login or Register  to view this content.

did - is just a shorter version.

So now using similar approach as with "fuel consumed" and the above input we end with array formula

Please Login or Register  to view this content.

You surely know, but may be not all readers, that array formula means:
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

see J25

[Ryan123]

Thanks Kaper

This seems to still round down rather than to the closest number 'Edit message' This works perfectly!!!!! thanks you very much Kaper!!!!!

Cheers
Ryan

[Kaper]

Glad to hear that,
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Thanks.