Array lists all values against a criteria

  • The attached spreadsheet has an array in cell M2:
    (This is copied down to M5, with the Row number changing automatically)

    The formula SHOULD look in Col B for all instances of the name in M1 (“STAFF”), and list the corresponding values from Col F into M2 – M5.

    Unfortunately it isn’t working.

    • The name “DR” occurs three times in Col B, but the array shows only two instances.
    • Whichever name is selected, all the values are “0” and not the actual values in Col. F.

    Any pointers received gratefully.

  • Re: Array lists all values against a criteria

    You need to add the -ROW($F$3)+1 part to get the row count to start at 1..... and also because you have .xlsx file, you can shorten the formula.....



    confirmed with ctrl+shift+enter and copied down.

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

  • Re: Array lists all values against a criteria

    Many thanks to both of you for the prompt amendments. Have to "mug up" on the "Row+1" concept, which I admit freely baffles me. Clearly a "two bottle" problem.

Participate now!

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