Excel Lookup Problem 1

[NigeW]

Hi there

I am wanting to perform the equivalent of the vlookup but when the lookup value does not find an exact match I want the nearest greater value to be returned. vlookup can only return the nearest lesser value.

I have created the following but this is reliant on the lookup data being sorted in descending order and so this isn't an ideal solution.

=INDIRECT(ADDRESS((MATCH(2500,H2:H7,-1)+1),9))

My data is:

Quantity Time
5000 60
3000 50
2000 40
1000 30
500 20
250 10

And so if a value of 1500 is entered I want '40' returned being the corresponding value for 2000 which is the next greatest value in the list.

Can anyone suggest any alternatives ?

Thanks

Nigel Wilson
Christchurch Web Design

 

[xlhelp]

Nigel.

Why not use the offset function in combination with your vlookup to get you the next cell?

Member- AAAA Association Against Acronym Abusers

 

[SkipVought]

Hi,

Try INDEX & MATCH
[tt]
=INDEX(Time,MATCH(1500,Quantity,-1),1)
[/tt]


Skip,

_[red][/red]

 

[NigeW]

H xlhelp and Skip

Thank you for your extremely prompt replies.

I have tried the index option and it works a treat.

Thanks again.


Nigel Wilson
Christchurch Web Design