Need Help with Index Collect Formula

Options
tchav
tchav ✭✭✭✭

Hello,

The formula i am trying to put together is below:


=INDEX(COLLECT({HBC Master File Product}, {HBC Master File Formula}, HAS(@cell, [HBC #]@row), {HBC Master File Plant}, CONTAINS(@row, "CBG COLD SPRING - G")), 1)


I want to search another sheet (HBC Master File Product) in the formula column to match the HBC#, the second criteria is to search the HBC Master file Plant column if it contains a key word. When i put in the first part, the formula works, it is when I am putting in the key word to look for that it says #unparseable.



Thank you,

Tags:

Answers

  • DKazatsky2
    DKazatsky2 ✭✭✭✭✭
    Options

    Hi @tchav,

    See if it works if you change "@row" to "@cell" in the CONTAINS function.

    Hope this helps,

    Dave

  • tchav
    tchav ✭✭✭✭
    Options

    Now it gives #INVALID VALUE

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @tchav

    Invalid Value will appear when the formula can't find a match for your two criteria. Can you test putting a filter on your source sheet with the two details:

    • The content in [HBC #]@row
    • "CBG COLD SPRING - G"

    Keep in mind it needs an exact match, so if any of your characters are out of place it won't find the relevant row.

    You can also test this by using COUNTIFS to see how many rows it finds in your source sheet:


    =COUNTIFS({HBC Master File Formula}, HAS(@cell, [HBC #]@row), {HBC Master File Plant}, CONTAINS(@cell, "CBG COLD SPRING - G"))

    If this says 0, it's not finding any matches.