Excel: Lookup problem

[D_Hailsham]

I want to create a lookup which returns data corresponding to a value equal or greater than the looked up value. If I use Vlookup it will always return the data relating to a value less than or equal.

For example: if I do a lookup for 1.5 and the first column of the array only contains 1 and 2, V lookup will return the value corresponding to 1. I want the value corresponding to 2.

 

[Doc Lenny]

Not sure if this will help, but an IF statement may do the trick.

=IF(A1>=F1,C1,"")

where A1 is the value you are looking up, F1 is your lowest value, C1 is value if true.

 

[empip]

Have a peek at this :-
http://www.cpearson.com/Zips/Lookups2.zip

In particular see the final 'Closest Match Lookup' example.

BTW .. Note the curlies - array formulae.

Any use

 

[D_Hailsham]

Doc Lenny

Not quite what I was looking for, but thanks all the same

empip

Very useful. Although it did not provide a complete solution, I did manage to work one out. My solution was sort the array in Descending Order and then use the Index and Match functions with a -1 as the last term in the Match.

Problem solved

Thanks guys.

 

[empip]

Doc Lenny

Not quite what I was looking for, but thanks all the same

empip

Very useful. Although it did not provide a complete solution, I did manage to work one out. My solution was sort the array in Descending Order and then use the Index and Match functions with a -1 as the last term in the Match.

Problem solved

Thanks guys.

Hmmm.. I thought this :-

Covered requirements - without sorting... All code supplied for nix.

 

[D_Hailsham]

Hmmm.. I thought this :-

Image deleted to save space

Covered requirements - without sorting... All code supplied for nix.

I'm sure you are correct. The problem is that it used functions and concepts with which I am not familiar, which meant that I found it difficult to understand exactly how it worked.

Sorting an array in reverse order was such an easy thing to do that I took the easy way out. When I have more time , I will study the use of arrays and the other functions.

 

[empip]

Possibly the best freeby Excel stuff on the web...
http://www.cpearson.com/excel/topic.aspx

'Array formulae' take you to the next level - awesome potential.