Better to use Filter function with multiple filter clauses put together by multiplying the conditions.

If the bands are defined in Table "BandDef" with columns [Band Start], [Band End] and [Band Name], and the value in the main table (say transaction level table where the band needs to be determined - table named "Transactions") is in column [Value],

then the formula would simply be as follows:

= Filter(BandDef[Band Name], ([Transactions@[Value]]>=BandDef[Band Start]) * ([Transactions@[Value]]<=BandDef[Band End]), "Apt error message for Band not found")

The same thing can be done without the table constructs by using usual ranges for cells and columns, but I personally like the excel Tables so much better.