lookup or match - Return nearest match

Hi

i wonder if someone can tell me what function or group of functions i need to look at to achieve the following

i have a list in a range

20
33
45
68
74

i then enter in a cell a value to use as a lookup

example 30

i need to do two things

look up the next largest number available from the range in this case it would be 33 and return that


if i enter 33 and since 33 exists in the list i need it jump to the next largest so returning 45 in this case.

i presume i can use two if statements but dont know the best way to work out the offset of moving to the next largest value and if i should use index and match or vlookup etc.

just looking for a pointer for the best way of doing it and also a bit of explanation of how i can use an offset to select the cell next to it or below it

Thanks a alot

Hi,

=INDEX(A1:A5,MATCH(A8,A1:A5,1)+1)

if your list is A1:A5 and your look up cell is A8

index returns the content of a cell in a range, eg

=INDEX(A1:C5,2,2) returns the second cell in and down in the array A1:C5

match identifies the position of a value in an array.

Hi there.

I looking for some help with choosing the best functions to do this simple task.

I have a range of numbers

20
34
63
54

So the users enters a number ie 25

in which i need to do a lookup or match for ... which i think i have got the basics off i need to test two options to return the result

if i select 25 i need to pick the largest number about it so in this case it will be 34 and return that

if the number exist in the list ie the user has entered 63 which is available to return that value

I tried the vlookup but dont know how to put the offset to move to the next higher value.

I dont know if that is the best way of doing it. and would rather learn a way which i can you to apply various offsets in columns and rows

Well i will look forward to hearing what people suggest.

Many thanks

Devo

http://www.excelforum.com/showthread.php?t=636366

sweep thank you very much for your reply..

sorry about the double post lol. i couldnt find my first post but did have a bit of a problem posting it .. .. ooops computer glitch at my end..

but appreciate the reply..

thank you

Nick Calladine
Blinds and Awnings in Nottingham

ckorp,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

Answer was correct. In your post we'll explain why is different now