Add the AND operator to an Array code

  • Hello
    this is my array formula which works - after a fashion! The formula shows a number of rows based on key pieces of data in B2 or B3 - however, I need the formula to only show information that has B2 AND B3 in it. I have tried AND and * in a number of places in this formula but no luck - any advice would be greatly received.


    Also, do you know if I can extend this formula to look at 3 pieces of information?


    Thank you.



    =IF(ISERROR(INDEX('[Service Data.xlsm]DATA'!$A$1:$S$5000, SMALL(IF('[Service Data.xlsm]DATA'!$A$1:$S$5000=$B$2, ROW('[Service Data.xlsm]DATA'!$A$1:$S$5000)),ROW(1:1)),12)),"", INDEX('[Service Data.xlsm]DATA'!$A$1:$S$5000,SMALL(IF('[Service Data.xlsm]DATA'!$A$1:$S$5000=$B$3,ROW('[Service Data.xlsm]DATA'!$A$1:$S$5000)),ROW(1:1)),12))

  • Re: Add the AND operator to an Array code


    Hmmm.. do you mean that B2 and B3 would then have to be equal for results?


    as in:


    [COLOR="#0000FF"]=INDEX('[Service Data.xlsm]DATA'!$A$1:$S$5000,SMALL(IF(('[Service Data.xlsm]DATA'!$A$1:$S$5000=$B$2)*('[Service Data.xlsm]DATA'!$A$1:$S$5000=$B$3),ROW('[Service Data.xlsm]DATA'!$A$1:$S$5000)),ROW(1:1)),12)[/COLOR]

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Add the AND operator to an Array code


    Hello
    sorry I wasn't clear. I have a spreadsheet with a number of rows of information - two fields on this spreadsheet relate to B2 and B3. The formula resides on a different spreadsheet and pulls through only those rows that match B2 and B3.
    thank you
    Julia

  • Re: Add the AND operator to an Array code


    Maybe you mean..



    [COLOR="#0000FF"]=INDEX('[Service Data.xlsm]DATA'!$A$1:$S$5000,SMALL(IF(('[Service Data.xlsm]DATA'!$A$1:$S$5000=$B$2)[COLOR="#FF0000"]+[/COLOR]('[Service Data.xlsm]DATA'!$A$1:$S$5000=$B$3),ROW('[Service Data.xlsm]DATA'!$A$1:$S$5000)),ROW(1:1)),12)[/COLOR]


    This should check and return result if either B2 or B3 values are found in your range.


    If that isn't working, please post a sample workbook with expected result

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Add the AND operator to an Array code


    Hi
    without any operator it gives the results as OR - I need to have results if both B2 & B3 are satisfied - however they hold different information. Would I not put a * where you have a + to make it AND?
    I will try your answer tomorrow but if not successful will post a brief sample tomorrow as I do not have a sample with me - apologies. Thank you

  • Re: Add the AND operator to an Array code


    I just realized your ranges are multi-column... I need to do some tests...

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Add the AND operator to an Array code


    Probably the best/easiest way would be to add a helper formula to your DATA sheet that checks each row for both B2 and B3.


    e.g. [COLOR="#0000FF"]=AND(ISNUMBER(MATCH(SHEET1!$B$2,A2:S2,0)),ISNUMBER(MATCH(SHEET1!$B$2,A2:S2,0)) [/COLOR] where SHEET1 would be replaced with reference back to workbook/worksheet where the B2 and B3 parameters are.


    copied down. This will give TRUE if both are found within the row.


    Then you can use your formula:


    [COLOR="#0000FF"]=INDEX('[Service Data.xlsm]DATA'!$A$1:$S$5000,SMALL(IF('[Service Data.xlsm]DATA'!$T$1:$T$5000=TRUE),ROW('[Service Data.xlsm]DATA'!$A$1:$S$5000)),ROW(1:1)),12)[/COLOR]


    where DATA!T1:T5000 contains the helper Formula.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Add the AND operator to an Array code


    So is the entry in B2 and B3 of the Dashboard always coincide with column E and G of the Data sheet?


    If so then you don't need the helper column, try:


    [COLOR="#0000FF"]=INDEX('[Service Data.xlsm]DATA'!$A$1:$S$5000,SMALL(IF(('[Service Data.xlsm]DATA'![COLOR="#FF0000"]$E$1:$E$5000[/COLOR]=$B$2)*('[Service Data.xlsm]DATA'![COLOR="#FF0000"]$G$1:$G$5000[/COLOR]=$B$3),ROW('[Service Data.xlsm]DATA'!$A$1:$S$5000)-MIN(ROW('[Service Data.xlsm]DATA'!$A$1:$S$5000))+1),ROW(1:1)),12)[/COLOR]


    [arf]*[/arf]


    substitute the column E and G references in red with actual Origin and Status column references

    Where there is a will there are many ways. Finding one that works for you is the challenge!

Participate now!

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