Formula to find between 2 numbers

[punit83]

SR# (Column A )	J# FROM (Column B)	J# TO (Column C)
1	96604	96606
2	96560	96586
3	96555	96559
4	96538	96539
5	96540	96542
6	96530	96537
7	96375	96384
8	96385	96427
9	96428	96457
10	96458	96465
11	96497	96521
12	96525	96527
13	96529
14	96545	96551
15	96466	96490

Hello,
Need formula to show result from SR#.
eg:-
if i enter 96605 than result should be showing 1
if i enter 96560 than result should be showing 2

Thanks in Advance

 

[hagia_sofia]

Hello, maybe:

=AGGREGATE(14,6,$A$2:$A$16/($B$2:$B$16<=96605)*($C$2:$C$16>=96605),1)

But what is the upper limit for SR#13 and what the result should be when the entered value is outside of range completely?

 

[punit83]

Hello, maybe:

=AGGREGATE(14,6,$A$2:$A$16/($B$2:$B$16<=96605)*($C$2:$C$16>=96605),1)

But what is the upper limit for SR#13 and what the result should be when the entered value is outside of range completely?

Hello,
Thank you for formula for SR#13 i will use if formula as of now.

If i want to use same formula for Text what shall i change in formula ?

 

[hagia_sofia]

Hello, a) what version of Excel are you using (2019 does not have UNIQUE), b) what do you mean by text - e.g. that column A will contain description instead of numbers?

 

[punit83]

Hello, a) what version of Excel are you using (2019 does not have UNIQUE), b) what do you mean by text - e.g. that column A will contain description instead of numbers?

as of now i am working on 2021.
yes description and no number ( i have another file on that i want to apply this formula )

 

[hagia_sofia]

Many thanks for your reply. With 2021 you could e.g. use (this will work both with numbers and texts):

=XLOOKUP(1,($B$2:$B$16<=96605)*($C$2:$C$16>=96605),$A$2:$A$16,"No Match")

or

=FILTER($A$2:$A$16,($B$2:$B$16<=96605)*($C$2:$C$16>=96605),"No Match")