vlookup up to the next increment

[Andy]

I was wondering if this is possible or not but here it is

I want to do a vlookup, but if the value that is question is greater that
what it finds it would go to the next value.
A B
1 30
2 25 12
3 35 10
4 50 8
5 65 6

So currently the formula looks like this vlookup(A1,A2:B5,2,true). With that
formula it will give me a value of 12, but i want to give me the b10 value
(10). Same goes if the value is 55 or 60, i dont want the returning value to
be 8, i want it to be 6.

I hope you guys can help

Thanks in advance
Andy

[Niek Otten]

Hi Andy,

=INDEX(B2:B5,MATCH(A1,A2:A5)+1)

--
Kind regards,

Niek Otten

"Andy" <[email protected]> wrote in message
news:[email protected]...
>I was wondering if this is possible or not but here it is
>
> I want to do a vlookup, but if the value that is question is greater that
> what it finds it would go to the next value.
> A B
> 1 30
> 2 25 12
> 3 35 10
> 4 50 8
> 5 65 6
>
> So currently the formula looks like this vlookup(A1,A2:B5,2,true). With
> that
> formula it will give me a value of 12, but i want to give me the b10 value
> (10). Same goes if the value is 55 or 60, i dont want the returning value
> to
> be 8, i want it to be 6.
>
> I hope you guys can help
>
> Thanks in advance
> Andy
>