Which excel formula should I use for this example?

Copper Contributor

I have a range of block addresses for each TRS and need to find the correct TRS to go with an individual address.  For example:

BlockStreetTRS LowblocknumberHighblocknumberStreetTRS
2972Abby  01000Abby37061A
    10012000Abby37061B
    20013000Abby37061C

 

What would be the best formula to use to populate the TRS into cell C2 with the data from cells E1:H4?

3 Replies

@ccoates 
Index and Match Function

 

=INDEX(H:H, MATCH(1, (A2>=E:E) * (A2<=F:F), 0))

You need to enter it with Ctrl+Shift+Enter instead of Enter only.

 

@Rr_ Thank you! I made this work with a XLookup formula.