MAX Value of Column Subset

I want to find the MAX value in a subset of a range of numbers.

Specifically:
1) I have a column of unique, non-sequential, 5-digit numbers ranging from 00001-50000.
2) From those numbers I need to identify any that have a value of <1000.
3) Of those identified, I want to know the largest value.

I'm guessing this probably isn't too difficult but I'm having a tough time thinking of or finding functions that will do this. Can anyone help?

Thanks.

Part 3 is the easiest part.

=MAX(A1:A5000) adjust the range to your needs.

Part 2 is a bit confusing. You want a list of all the numbers greater than 1000? Where do you want the list to be populated?

Is it possible that you post a .zip sample of your workbook?

Yes, I want the result of =MAX(A1:A5000) but with an exception-- I want the MAX of only those values in A1:A5000 that are less than 1000. So, for example:

A1 = 00100
A2 = 02350
A3 = 27000
A4 = 00125
A5 = 01000

I want to be able to identify the value 00125 (i.e. =MAX(A1:A5) of all values that are less than 1000). Does that make sense?

=MAX(IF(A1:A5000<1000,A1:A5000))

NOTE: This is an array formula. For it to work, you must press CTRL+SHIFT+ENTER (not just enter)

Let me know if that works.

Works perfect! Thank you.

Glad I could help.