INDEX/COLLECT returns #UNPARSEABLE error

LeAndre P

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,

Christina09

@LeAndre P

Try this:

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

Christina09

@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

LeAndre P

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

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)

LeAndre P

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

@LeAndre P

Try this:

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

LeAndre P

That did it. Thank you!!