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


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

3 Replies

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.