comparing two values in same column with another column using AND condition

Column A is MOBILE NO.
Column B is Agent Id calc

MOBILE NO     Agent Id       
7024226667    A1EXF8ZF  unique
7042004489    A1XKV3J1  unique
7042004940    A1JA23X1  unique
7042005480    A1UFIHOD  unique

Formula in excel they have used column calc is

IF(A2="","Unique",IF(AND(A2=A3,I2=I3),"Multiple","Unique"))

I’m trying in sql server to implement the logic.

I’ve added some NULL values just to check all cases.

Play with it here: http://rextester.com/NUPSTW29373

create table #val (mobile_no varchar(10), agent_id varchar(10));
insert into #val values
('7024226667','A1EXF8ZF'),
('7042004489','A1XKV3J1'),
('7042004489','A1XKV3J1'),
('7042004940','A1JA23X1'),
(null,'A1JA23X1'),
('7042005480','A1UFIHOD'),
('7042005480','A1UFIHOD');


SELECT 
    mobile_no, 
    agent_id,
    CASE WHEN
        mobile_no IS NOT NULL 
        AND LEAD(mobile_no) OVER (ORDER BY mobile_no) = mobile_no
        AND LEAD(agent_id) OVER (ORDER BY mobile_no) = agent_id
        THEN 'Multiple'
        ELSE 'Unique'
    END AS [Calc]
FROM #val
ORDER BY mobile_no;


+------------+----------+----------+
|  mobile_no | agent_id |   Calc   |
+------------+----------+----------+
|    NULL    | A1JA23X1 |  Unique  |
+------------+----------+----------+
| 7024226667 | A1EXF8ZF |  Unique  |
+------------+----------+----------+
| 7042004489 | A1XKV3J1 | Multiple |
+------------+----------+----------+
| 7042004489 | A1XKV3J1 |  Unique  |
+------------+----------+----------+
| 7042004940 | A1JA23X1 |  Unique  |
+------------+----------+----------+
| 7042005480 | A1UFIHOD | Multiple |
+------------+----------+----------+
| 7042005480 | A1UFIHOD |  Unique  |
+------------+----------+----------+

• I assume your Excel data is sorted.
• Consider giving a little bit interesting data sample

create table t ([MOBILE NO] varchar(100),[Agent Id] varchar(100));

 insert into t ([MOBILE NO],[Agent Id]) values
     ('7024226667','A1EXF8ZF')
    ,('7042004489','A1XKV3J1')
    ,('7042004940','A1JA23X1')
    ,('7042005480','A1UFIHOD')

select      [MOBILE NO]
           ,[Agent Id]

           ,case 
                when count (*) over (partition by [MOBILE NO],[Agent Id]) = 1 
                then 'Unique' 
                else 'Multiple' 
            end                 as [Unique/Multiple]

from        t

+------------+----------+-----------------+
| MOBILE NO  | Agent Id | Unique/Multiple |
+------------+----------+-----------------+
| 7024226667 | A1EXF8ZF | Unique          |
| 7042004489 | A1XKV3J1 | Unique          |
| 7042004940 | A1JA23X1 | Unique          |
| 7042005480 | A1UFIHOD | Unique          |
+------------+----------+-----------------+