this worked with your 11 values in A2:A12

=MATCH(IF(A2>1000,A2,B2+0.000001*A2),LARGE(IF($A$2:$A$12>1000,$A$2:$A$12,$B$2:$B$12+0.000001*$A$2:$A$12),{1,2,3,4,5,6,7,8,9,10,11}),0)

Entered with Ctrl+Shift+Enter Rather than just enter in C2, then drag filled
down to C11.

--
Regards,
Tom Ogilvy


"sa02000" wrote:

>
> I need to rank a list based on two columns. So, first I have to rank
> all
> the data based on column1 if value is above certain no. but if cells
> have same values then rank those particular cells based on column2 and
> once the value in column 1 is below that threshhold rank based on
> column2 but if cells have same values (in column2) then rank those
> particular cells based on column1.
>
> Here is how the logic flows....
> If column A value is greater than 1000 then First rank by columnA, if
> conflict in ranking (same value in column A) then rank by columnB,
> else
> rank by columnB, if conflict in ranking (same value in columnB) then
> rank by columnA again.
>
> Here is an example
> ColumnA....ColumnB.....Rank
> 4999....2.56......1 <<colA is greater than 1000, but rank based on
> colB
> 4999....1.59......2<<colA is greater than 1000, but rank based on colB
> 3149....3.59......3<<rank based on columnA
> 2482....0.00......4<<rank based on columnA
> 1712....0.00......5<<rank based on columnA
> 1422....0.73......6<<rank based on columnA
> 184......4.73......7<<colA smaller than1000, so rank based on colB
> 554......0.00......8<<colB has same values, rank using 4colA values
> only
> 377......0.00......9
> 298......0.00......10
> 196......0.00......11
> and so on.....
> I tried this but obviously it gives me same rank number where column A
> and column B has same values...
> =IF(A2>=1000,RANK(A2,$A$2:$A$42,0),RANK(B2,$B$2:$B
> $42,0)+COUNTIF($A$2:$A$42,">=1000"))
>
> from this I get rank as following...
> 1
> 1
> 3
> 4
> 5
> 6
> 7
> 8
> 8
> 8
> 8
> I hope this is clear enough...
> Thanks, Jay
>
>
> --
> sa02000
> ------------------------------------------------------------------------
> sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747
> View this thread: http://www.excelforum.com/showthread...hreadid=533404
>
>