Multiple returns with 2 criteria from another workbook

  • Hi,


    I hope to get some help here with a problem that I have.


    I have a workbook that retrieves information from different workbooks, this all works well.

    The main problem I have is when there is more then 1 result.


    Please see attached file with a sample.


    Just to clarify the sample is retrieving on a different sheet within the same workbook. The solution needs to be that it needs to retrieve that information from a different workbook.


    Hope you guys can help me out, I would really appreciated it.


    Thanks


    Production Reports_WIP.zip

  • Hello,


    If I am not mistaken, based on your explanations, in the Overview sheet, you should only get the results shown ... since there is no other instance of your combined search for both 13 01 2022 along with A4 ...


    Please clarify

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Hello,


    Feel free to build another example to illustrate your potential problem ...


    And be confident about the solution ... since multiple returns with multiple criteria is indeed possible with an Array Formula ;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Hello,


    To clarify things ... have adjusted with a red font your original data ... so that you can exactly see how the array formula retrieves the first, the second and the third instances ...


    Hope this will help

  • Carim


    Holy moly, that did the trick. I made it work in the original file, it works like a charm.

    You made my day, thank you so much

    Glad you could fix your problem :)


    Thanks for your Thanks AND for the Like 8)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Carim Just a quick update:


    I have successfully implemented the solution, see code below, and it works perfect. All the information is displayed how it should.


    Code
    =IFERROR(INDEX('Database'!$T$1:$T$2002,LARGE(IF(F28&G28&$AW$8='Database'!$B$1:$B$2002&'Database'!$C$1:$C$2002&'Database'!$X$1:$X$2002,ROW('Database'!$C$1:$C$2002),0),SUM(('Database'!$B$1:$B$2002=F28)*('Database'!$C$1:$C$2002=G28)*('Database'!$X$1:$X$2002=$AW$8))-I28+1)),"")

    The retrieval of the information is working without any problems, however when I try to SUM the results it gives me a #value error.

    The code I am using, see below, works perfectly when I type in the value manually, but when used with the first code it gives me an #Value error.

    I tried to change the condition of the cells to Number / General, but nothing seems to help.

    Code
    =SUM(($AO$28:$AO$55+$AP$28:$AP$55+$AQ$28:$AQ$55)*(--($D$28:$D$55=D24)))/60


    Would you be able to assist ?

    Thank for any help you can give me.

  • Hello,


    There is no reason which would explain why you cannot perform a Sum ...


    Could you attach a sample with just 10 to 15 rows to illustrate the problem ?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Difficult to guess what might have happened ...


    1. Make sure there are no empty cells or text cells within your arrays ...

    2. Arrays can be very "greedy" in terms of memory ... so just check the total number of formulas you are using ...


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Pleased to hear you have all sorted it out :)


    Thanks a lot for your Thanks AND for the Like 8)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!