If I understand the logic correctly, shouldn't 1500 return Test2 for the same reason as 1001? Maybe it was a typo and you meant 2500; if not, I'll definitely need the logic explained 🙂

Otherwise, it seems like you're just wanting the  name corresponding to highest value in the table that is less than some value you pass in, so something like this:

DECLARE @test-2 TABLE(Id INT,[Name] VARCHAR(50), [Value] INT);
DECLARE @some_parameter INT=60;

INSERT INTO @test-2 
select 1,'Test1',0    UNION ALL
select 2,'Test2',1000 UNION ALL
select 3,'Test3',2000 UNION ALL
select 4,'Test4',3000;

SELECT TOP 1 [Name]
FROM         @Test
WHERE        [Value]<@some_parameter
ORDER BY     [Value] DESC;

One thing not entirely clear from the examples is what happens when the value passed in is equal to a value in the table. Should a passed value of 1000 return Test1 or Test2? If it should return Test1, then the query works as-is. If it should return Test2, then the '<' should be changed to '<='.

Cheers!