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.

Hi

Just did this in the example file that you provided.

BS1: '00201 (note the ' is important - it makes the item text to make sure the leading zeros are not removed)
BT1: =MATCH("*"&BS1&"*",B:B,0)
BU1: =MATCH("Total",OFFSET(B1,BT1,0,10000,1),0)
BV1: =INDIRECT("H"&BT1+BU1)

If that consistently brings back the value that you require, great! If not, can you give some more examples based on your file, of exactly what you want returned from what situation.

rylo

PL SEE THE ATTACHED FILE WITH FORMULA IN BQ1 CEELL

ARRAY FORMULA

Please Login or Register  to view this content.

You guys rock, this did the trick!! Thanks for the replies!

Both of your ways work. However, what I am trying to do is in another excel workbook. That being said I have everything working with a caveat:

I can not find a way to use the INDIRECT function to reference a closed workbook.

Attached is my file 'Example' and what I am searching in "Example Support'.

Any way to do this?

Thanks

Hi

INDIRECT does not work on closed files. From the help file on the INDEX function

If ref_text refers to another workbook (an external reference), the other workbook must be open. If the source workbook is not open, INDIRECT returns the #REF! error value.

But MATCH and INDEX both seem to work with closed files.

rylo

You can try this

Please Login or Register  to view this content.