Multiple Matches Cross Referencing Multiple Tables

[Cobra17]

updated for clarity: hopefully

Based on D23 & D24, I need to find:

M53 = TempLow = value lower or equal to D23, but is located next to the "Table" in D24 ( if D23 = CS-1 & D24 = 405... M53 = 300 ....but if D23 = HA-1 & D24 = 405, M53=500)

M54 = TempHigh = value higher or equal to D23, but is located next to the "Table" in D24 ( if D23 = CS-1 & D24 = 405... M53 = 400 ....but if D23 = HA-1 & D24 = 405, M53=700)

and so on..

** the library might open but be hidden.

[CK76]

You can't use Indirect on another workbook when it's closed. INDIRECT only works on open workbook.

Personally, I'd just use PowerQuery to load data from source into Shell workbook, passing D23 as variable/parameter to PowerQuery to select the table.
Then using D24 to return pertinent data.

Though I'd recommend setting up each table as Excel Table and/or separating it out into separate sheets in Library workbook.

[Cobra17]

I did think of a possible way around it... but I'd like to get the indirect working if possible.

my work around would just be putting all the tables in the same columns and adding a column for the table name.. then i look up the table name, in the column and A and then find B.. like i do for M56 and a few other cells.

[Cobra17]

The work book is always open in the background.

I'm not familiar with PowerQuery

[CK76]

Can you upload sample that reflects your proposed set up?

[FDibbins]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[Cobra17]

I updated my first post and added updated tables and calc sheet.

[AliGW]

I think you've made it worse! Try this: "Multiple Matches Cross Referencing Multiple Tables".

[FDibbins]

I updated my first post and added updated tables and calc sheet.

Thanks for the effort, however, a title needs to explain (briefly) what you are trying to do, not what you think may work. I understand that you think MATCH would be involved - and maybe it will, but let the experts decide what you need to use
Hope that makes sense to you?

[Cobra17]

maybe i made it better... maybe not.

It's hard to explain something when you've been focused on it and it makes sense to yourself... and i'm not super great with excel to start with.

[FDibbins]

Yes I understand that totally lol (Ali suggested a title, so I changed it to that for you )

[CK76]

Hmm, with your current setup. Provided that you have Library workbook open...

You can leverage named range in the Library workbook. Make sure that all Named ranges referenced in this manner is set to Workbook scope.
In M54:
=INDEX(INDEX(INDIRECT("'BDD Library.xlsm'!" & M53),0,1),MATCH(D24,INDEX(INDIRECT("'BDD Library.xlsm'!" & M53),0,1),1))

EDIT: Internal INDEX is used to return all rows for column 1 of named range. row or column argument value of 0 in index function will return either all row or column.

[Cobra17]

CK76, That's how I'd like to do it. I tried putting in your code but I can't seem to get it working.


I'm assuming:
M53 is the table name
D24 is the temperature i'm looking for


i figured out why... i was using a temp lower than on my chart and didn't have anything to say use the lowest temp then.