INDEX/COLLECT returns #UNPARSEABLE error

I am trying to pull PO totals over from one sheet to another where unique Claim ID numbers match that are present in both sheets. However, I keep getting the #UNPARSEABLE error msg. 

Formula:

=INDEX(COLLECT({PO Value},{PO Value}@cell <> "", {Claim ID}@cell = [Claim ID]@row), 1)

1) {PO Value}: this is the source range that contains the data I need populated in the target sheet

2) {Claim ID}@cell: This is the range where the unique identifier (Claim ID#) is located in the source sheet

3) [Claim ID]@row: This is what I am trying to match between the two sheets in anticipation of collecting the PO Value

thoughts?

Thank you,

Best Answer

Answers

  • Christina09
    Christina09 ✭✭✭✭✭✭
    edited 10/11/21

    @LeAndre P

    I don't think you need @cell

    try this:

    =INDEX({PO Value}, MATCH([Claim ID]@row ,{Claim ID},0))

    or

    =INDEX(COLLECT({PO Value},{PO Value} <> "", {Claim ID} = [Claim ID]@row), 1)

    Hope this works

  • Thank you Christina that seems to work however, when I convert to a column formula I get get #NO MATCH for claim ID items that I have yet to issue a PO for. Is there any way to return $0.00 for items w/o a PO Value? 


    Thank you,

  • Kelly Drake
    Kelly Drake Overachievers Alumni
    edited 10/11/21

    You can wrap around an IFERROR ....

    IFERROR(INDEX({PO Value}, MATCH([Claim ID]@row ,{Claim ID},0)), 0)

    OR

    IFERROR(INDEX(COLLECT({PO Value},{PO Value} <> "", {Claim ID} = [Claim ID]@row), 1), 0)

    Kelly Drake (she/her/hers)

    STARBUCKS COFFEE COMPANY| business optimization product manager

  • Thank you, Kelly that solved one problem and put a BIG spotlight, on the possibility of having multiple POs issued to one unique claim ID#. Currently the formula is matching the claim ID# and returning a desired results, however, it is only returning one value instead of the sum value of all claim ID#’s that match when more than one PO is issued for the same claim ID#. This discounts much needed expenditure data.

    The sheet in question, the target sheet is basically a 1to1 repository meaning it will only contain one claim per one unique claim ID#. Each row will have a different(new) claim and claim ID#. Respectively, the PO Log sheet (the source sheet) in this case is where each PO that is issued is recorded and managed according to the claim ID#. Duplicate claim ID# PO’s rest here and have different PO#’s. Would a SUM formula be more fitting for this situation instead of the index formula?

     Formula:

    =IFERROR(INDEX({PO Value}, MATCH([Claim ID]@row, {Claim ID}, 0)), 0)

    Source sheet:

    Target Sheet:

    Thank you,

  • Christina09
    Christina09 ✭✭✭✭✭✭
    Answer ✓

    @LeAndre P

    Try this:

    =IFERROR(SUMIFS({PO Value}, {Claim ID}, [Claim ID]@row),0)

  • That did it. Thank you!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!