vlookup question

[c0ysw]

in the linked screen shot, im trying to get the rest of the staff filled in, and its not happening properly. for example, when i fill it through to B11 i want it to show 6 as that is the amount of staff needed, but it shows 4


thanks in advance

sorted, cheers

[WinteE]

You'd better use a formula like =ROUND.UP(9001 / 4500,0) * 2 to calculate how much staff you need.

The function ROUND.UP may be false, as I translated it from the Dutch function for this (AFRONDEN.NAAR.BOVEN).

Erik

[c0ysw]

thing is, i need to be able to enter different numbers into the predicted meals table and have it adjust the staff table automatically. my problem is that vlookup chooses either an exact or lower value, i need it to choose the next higher so there is enough staff to cover

[WinteE]

Instead of 9001 you can also point to a cell :

ROUND.UP( B4 / 4500 , 0) * 2

[martindwilson]

if you use index/match instead of v look up
if the data is sorted as descending
then setting the match type to -1
will return smallest value that is greater than or equal to match value
here's how its done
formula is in b2
i put the "if " bit in because it will find a match for nothing which is annoying
see example in g2

enter number dining in a2

[c0ysw]

you sir, are a legend