Find Highest or Closest number from list

Well a sample workbook would help greatly, you could try something like this though :
C1:

Formula:

Please Login or Register  to view this content.

HOWEVER, this will only work if the numbers in column A are sorted;
With a sample work book, I could probably come up with a sumproduct formula to work with unsorted column A....or anyone else here could

Try This

Formula:

Please Login or Register  to view this content.

For any explanation go to

http://www.excelforum.com/excel-gene...est-value.html

Also perhaps =IF(MIN(IF(A1:A20>H1,A1:A20))=0,MAX(IF(A1:A20<=H1,A1:A20))) were H1 contains your value.

Commit with Ctrl+Shift+Enter

I tried your formula it is returning a false error

See..3 different respondents, 3 different answers!
This is what I love about this site !
IF I got three the same answers, I would be thinking "that must be the best way.."
BUT, with 3 different ones, you get to test and choose!!!

Who's?
If it's mine, then your data is NOT sorted, and you will probably wind up with errors...thats why I said the data MUST be sorted... sorry

this has to be in C1

=IF(ISERROR(LARGE(A1:A493,SUMPRODUCT(--(A1:A493>=B1+1)*(A1:A493<>"")))),SMALL(A1:A493,SUMPRODUCT(--(A1:A493<=B1-1)*(A1:A493<>""))),LARGE(A1:A493,SUMPRODUCT(--(A1:A493>=B1+1)*(A1:A493<>""))))

frankly speaking i have taken some references for this this works

Edit:

i have considered data in A1 to A493, B1 as value to be match, C1 is result cell

The IFERROR functoin will fail if the OP is using 2003 as profile suggests.. just a friendly reminder

Ohhhh.. my mistake ,

this will be good i think thanks dredwolf


=IF(ERROR.TYPE(LARGE(A1:A493,SUMPRODUCT(--(A1:A493>=B1+1)*(A1:A493<>""))))=6,SMALL(A1:A493,SUMPRODUCT(--(A1:A493<=B1-1)*(A1:A493<>""))),LARGE(A1:A493,SUMPRODUCT(--(A1:A493>=B1+1)*(A1:A493<>""))))

@rajeshturaha

Note that the OP stated: "the closest number higher than the number in B1, or (if there are no numbers higher) return the closest number lower."

This would seem to contradict the basis of your formula, which will always return the absolute closest.

Regards

@ amy_d2, Not a problem
(although...I still think your are using functions not available to 2003 users, at least not without using the built-in macros, which have to be coded in in the named 'Formulas', and macros allowed, ... Error.Type is not one of the "off the shelf" macros I remember... )

@Pepe Le Mokko

"Also perhaps =IF(MIN(IF(A1:A20>H1,A1:A20))=0,MAX(IF(A1:A20<=H1,A1:A20))) were H1 contains your value"

This is good, but you simply left off your the FALSE part of your statement!

Correct to e.g.:

=IFERROR(1/(1/MIN(IF(A1:A20>H1,A1:A20))),MAX(IF(A1:A20<=H1,A1:A20)))

Again, array-entered.

Regards

@ XOR LX,
Again, the IFERROR function is contra indicated, profile suggests only 2003 version, if your going to use an iferror, then supply the VBA to support it , please !

@dredwolf

Apologies. Really shouldn't have forgotten that, especially since you only mentioned it a couple of posts back!

Time for another coffee!

In that case, Pepe's formula will just have to be extended the long way round:

=IF(MIN(IF(A1:A20>H1,A1:A20))=0,MAX(IF(A1:A20<=H1,A1:A20)),MIN(IF(A1:A20>H1,A1:A20)))

Regards

LOL
is not a problem for me, we just have to keep our public informed, if we are goig to use something they don't have, we better be able to supply it too them

Of course, if Microsoft had got round to introducing that rather obvious formula 15 years ago...

True...
But then us people trying to be 'Guru''s would have nothing to do...

This should work for any version of Excel.
The numbers can be in any order.

Where A2:A32 is the range, and C2 the target.

Formula:

Please Login or Register  to view this content.

[EDIT]
Oops! This returns the nearest or equal, that's not what the problem is ...