VLookUP problem

[Guest]

When creating a vlookup and you want it to return the closest match in the
lookup table rather than an exact match, I know that you don't enter FALSE
into the formula. However, when it cannot find an exact match, it
automatically returns the lower figure from the lookup table. For example, I
may have a figure of 10,750 in my main table and am asking it to search for
the closest match in the lookup table.
The lookup table contains various figures, including figures 10,000 and
11,000 but for £10,750 it returns £10,000 instead of the £11000.

How can you ask it to return THE closest match??

Hope this makes sense...

Thank you
Louise

 

[Pete_UK]

Hi Louise,

Your table needs to be in ascending order. What values do you have in
the first column of your table? If they are regular amounts, like 1000,
2000, 3000 etc, then you could apply a ROUND function with 500 added
on, eg:

=VLOOKUP(ROUND(A2+500),table,2)

Hope this helps.

Pete

 

[Pete_UK]

Slight amendment:

=VLOOKUP(ROUND(A2+500,-3),table,2)

You might also like to check out the CEILING( ) function.

Pete

 

[Guest]

Hello again.......

My table is in ascending order and looks like:

Amount earned Percentage due
£- 0%
£10,000.00 1%
£15,000.00 2%
£20,000.00 3%
£25,000.00 5%

I am trying to calculate what bonus people get, depending on how much
commission they have earned. One of my people has earned £13,350 which is
obviously closer to £15k than £10k but it only returns 1%.

is there not an easier way of doing this than adding the round function?

Thanks again.
Louise

 

[Pete_UK]

Surely your table represents thresholds - you only get a 2% bonus if
you earn £15k, so the normal operation of VLOOKUP would work in this
case.

I don't think that adding the ROUND function makes it more difficult !
<g>

Pete