Excel Help

scheister

Sorry if this is the wrong place to post this mods feel free to move it.

Im looking for a bit of help with an excel formula.
What im trying to do is a template for working out the BIK on cars. So the part im stuck is getting a formula that will look up in a table the mileage and the emission category and return the relevant percentage.

The issue is the milage will be in ranges aswell

Any help is appreciated

helimachoptor

Post what you have attempted so far

scheister

This is what i have so far. Have it at the stage is i land on the exact figure say 24000 returns a value is how to get it to look within ranges im stuck on

helimachoptor

I'd probably just do an If statement with a nested Vlookup , im sure theres a better way to do it,

**Timbuk2**

In case you haven't got this sorted yet.

Two ways you could go about it.

Option 1 (longest formula, but easily understood):
Like helimachoptor said, nested if statements. Might help if you make a helper cell which assigns group numbers to each band (e.g. 0-24000 = 1, 24001-32000 = 2, ...) and then put a column of (1, 2, 3, ...) beside the table you are currently using for the lookup and use it instead.

So your nested if statement in helper cell could look like =if(B8<=24000, 1, if(B8 <= 32000, 2, if(B8 <= 48000, 3, if(..... ))))

You could use something more descriptive than "1, 2, 3, ..." e.g. "0-24000, 24001-32000, ..." if you wanted but it makes the formula longer and does the same thing.

Option 2 (shorter formula, less easily understood):
In the vlookup statement, the last (optional) argument specifies whether you are a looking up a range or exact value (TRUE=range, FALSE=exact value).

If you don't specify an argument (it seems you haven't), then Excel uses TRUE as the default. When TRUE is specified, if an exact match is not found, Excel returns the "closest match" that is less than the lookup value. Thus, you need to alter your vlookup statement so it is looking up the lower bound (I can't tell because the column letters are hidden but I think you might be using upper bound).

I've attached a very simple spreadsheet showing this - incorporate this into your vlookup statement for a quick and easy solution.

Just a quick note about using the 'TRUE' argument for vlookup - it is the default behaviour for vlookup (i.e. if you omit the argument it will assume true), however it is probably the least commonly needed. If using vlookup for exact lookups (which will be most of the time), you should explicitly specify FALSE.
Also, TRUE also assumes that the lookup column is sorted in ascending order (which it is in this case). If this is not the case it won't give sensible results.

I hope this makes sense - please ask me if it doesn't!

scheister

thanks everyone got it sorted now grouped it as suggested then used the vlookup and match to search using the group. So think number 2 with a slight change.