Finding & Returning Data Based on 2 Parameters from a Separate Workbook

Hi,

I am in need of some help trying to figure out a way to return cell data from a separate workbook into my workbook, based on 2 search parameters.

-My workbook contains a list of rooms (name and number) within a building, and a respective value associated to that room.

-My other workbook contains a large list of rooms, along with a lot of other information associated with those rooms, however I would like only 1 of those values. The large list of rooms is not all contained in 1 column or row, but many (one room fills A1-A497 and BO1-B497) and I have anywhere from 50-250 rooms, depending on the building. The problem also is that the room title is a text string and the number would be inside that string.

For Example:

I would like to search for room number 00102A from my workbook, and return the value "Total" underneath the heading "COOLING COIL SELECTION".

I tried using a match function, but it wouldn't match the room number because it was buried within a string. Also even if it did find it, I was stumped trying to get this to to search again to find "COOLING COIL SELECTION" and return a value below it.

I attached the file that I want to search into.

If anyone could help I would greatly appreciate it.

Thank you.