hi there. i hope that someone can help me with this:

in sheet 1

a b c

1 free reduce paid

2 ? ? ?

note: (free means a numbers between 1000-1999, reduce means a number between 2000-2999, and paid means a number between 3000-3999)

in sheet 2 the numbers

a b

1 john doe 1344

2 jane doe 2416

3 jerry jones 1855

4 thom harry 3664

5 joe smith 3062

6 terry david 2732

now in sheet 1

in cell a2 - i need a formula that will give me the max number that is indicated in sheet 2 for free only-giving me a result of 1855 because it is the maximum number between 1000-1999.

i tried =max('sheet2'!,$b$1:$b$6)

but it gave me a result of 3664 because it is the max number, but i need the max number between 1000-1999 in a2, 2000-2999 in b2, 3000-3999 in c2 and so on.

i hope i did not confuse anyone.

thanking you all in advance