IFS(AND()) ACROSS MULTIPLE WORKBOOKS

[ADKINSM_DTE]

I need to have a cell search multiple workbooks and based on criteria matching the original workbook will return the contents of another cell in the matching worksheet of that workbook. 

Example:

IF( H4=D2 & A4=A2 (Across Work book2, Workbook3, Workbook4, Workbook5) on any of the worksheets. Then return the contents (from which ever worksheet matches criteria) of cell C2.

I have tried IFS(and(or)) 

I tried INDIRECT()

I have no idea what else to try seeing VLOOKUP needs to have data in a-z order and mine is not. 

[#2]

@ADKINSM_DTE 

Hard to say....if it's possible for you to post copies or mock copies of several examples, we might be able to try different "tricks"

Absent that, I've successfully used FILTER to retrieve data from other workbooks, and it works with multiple complex criteria. It's a relatively new function, requiring the current version of Excel, but very powerful. Here's a link that explains it well.

[#3]

This is something that gets easier with 365 and the stacking functions available. However, dynamic arrays (and INDIRECT, for that matter) require the workbooks to be open. Otherwise, a #REF! error may happen on refresh.

I suspect this might be something better suited for PowerQuery.

[#4]

@mathetes 

Here in the original pic you can see the formula I am struggling with.

I am trying to get it to: (into cell E4:E50 on the original Worksheet) =IF the MUNI Page & ATS matches(*from the other 4 work books on any worksheet) to return the value in $C$2 of the matching worksheet(no matter which sheet matches).

**Also important to note, I will be adding more worksheets to each of the 4 Workbooks as needed.

Hopefully this makes sense & thank you for all the help.

[#5]

@ADKINSM_DTE 

This is far more complicated (from the appearance of things) than that first one, and on an altogether different set of data.

1. May I, first, suggest you start a new thread...so that others are more likely to see it.
2. Second, is it at all possible for you to grant us access to either the real spreadsheet(s)/workbook(s)..post them here, if you can, OR put a copy on OneDrive or in GoogleDrive or one of the other cloud services and then give us a link here. Just make sure that no confidential or proprietary info is included; no name of real people or businesses as well. So a mockup would also work......

[#6]

Ok I will start a new thread and remove names and attach sheets if I can.
Thank you for all of your help.

[#7]

https://techcommunity.microsoft.com/t5/excel/ifs-and-across-multiple-workbooks-returning-data-from-m...

I started a new thread here and I attached mach workbooks similar to what I am using.

Thank you ahead of time.