Compare a value with series of cells and display output

[brijeshkeni]

Hi All,


I need to compare a value in a cell; say 'D4' with series of numbers; say 'F2:F9'.


lets say the values in each cell from F2 to F9 are:

F2 - 2

F3 - 4

F4 - 5

F5 - 6

F6 - 7

F7 - 8

F8 - 9

F9 - 10


If D4=6; then i would need a formula which would compare all values from F2 to F9 and display value 7 (in cell F6) which is immediate although 8, 9 and 10 are also greater than 6. i would like to compare with the nearest greater number than the value in D4 and display it in any other cell.


Can you please advise?


Best Regards,

Brijesh

 

[Luke M]

Just to clarify, are you looking for the next number in order of magnitude, or in sequence? Or, if numbers are always sorted, this question is moot.


If the latter, this should work:

=INDEX(F2:F9,MATCH(D4,F2:F9,0)+1)


If the former, this one:

=LARGE(F2:F9,RANK(D4,F2:F9)-1)

 

[brijeshkeni]

Hi LukeM,

Thank you for your valued information. What i was looking for is as defined below:

we have three columns A,B,C and rows 1 - 6 as shown below. what i want to do is for example if i have 340 mangoes, the formula would give me search column A and give me OUTPUT 350. can you advise if this can be possible.

[pre]

A        B                   C
150 	FS-20121	 0-150 mangoes
200 	FS-20122	 151-200 mangoes
250 	FS-20123	 201-250 mangoes
300 	FS-20124	 251-300 mangoes
350 	FS-20125	 301-350 mangoes
400 	FS-20126	 351-400 mangoes

[/pre]

 

[Luke M]

The first formula I posted will meet your needs then.

 

[brijeshkeni]

Hi Luke,


I tried the formula but I am getting an error as '#N/A'. Can you please advise. probably the problem is in the MATCH function


Best Regards

Brijesh

 

[Luke M]

Oops, you're right. Need to take out the 0.

=INDEX(F2:F9,MATCH(D4,F2:F9)+1)

 

[brijeshkeni]

Hi Luke,


Thanks a lot for you expertise.

It works fine now..


Best Regards,

Brijesh

 

[Luke M]

Glad to help, sorry for the earlier confusion I caused.

 

[brijeshkeni]

Hi Luke,


there was no confusion.. on the positive end i learnt some new functions..thank you the same.

I am not new to excel but am in the intermediate level


Best regards,

Brijesh