IFS(AND()) across multiple workbooks returning data from matched cell.

Occasional Contributor

I am trying to bring data into a specific cell of a worksheet. I need it to search 4 other workbooks on all contained worksheets & compare values from 2 cells on original worksheet to make sure they match then return the information from a 3rd cell to the original worksheet. 


I hope that isn't too confusing. 


See attached workbooks and sheets. 


Here in the original workbook (ATS TEST) you can see the formula I am struggling with(CELL:E4)

I am trying to get it to: (into cell E4:E50 on the ATS TEST Worksheet) =IF the ID 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.

0 Replies