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  |
+------------+----------+----------+