Cross sheet functioning column formulas working perfectly...except for two rows?

DWalkerUK

Problem Summary:

I have a series of formulas and cross sheet references which grab display data that meet certain conditions. They are working consistently across usage…except for two rows? For data sensitivity, I'll only be including the date fields out of the display data, but the same error applies to all final display data. But only for these two rows.

Intrigued, as it's all column formulas, but I'm sure we'll be able to find a flaw somewhere that I've missed!

System Overview:

Source sheet rows have an identifier, a checkbox condition, and then display information including a date field.

A second sheet aims to collect instance ID numbers which meet the condition. Then it uses that instance ID number to return the additional details, including date which meet that condition.

Source sheet (Initial Incident Reports) relevant columns:

S1: ID Ref:
Autonumber for referenceID

S2: Action Required?
Manual check box

S3: Incident date
Manual date field

Display/Helper Sheet (Reporting Action Plan) relevant columns:

ROWID
Autonumber counter from 1 for COLLECT assistance

Action ID REF
Collects all S1{Initial ID Ref} which have an S2{Initial IsAction} in row value of 1. Returns the value which has a collection position of ROWID@row.
=INDEX(COLLECT({Initial ID Ref}, {Initial IsAction}, =1), [Row ID]@row)

Incident Date:
Returns S3{Initial Inc Date} where S1{Initial ID Ref} matches Action ID Ref.
=INDEX({Initial Inc Date}, MATCH([Action ID REF]@row, {Initial ID Ref}))

Problem details:

As you might be able to see, display for 066 and 071 show dates 11/01/24, which matches ID REF 065. For 066 and 071, all display data is from 065.

Any help would be greatly appreciated!

Reference list for thoroughness:

S1: {Initial ID Ref}

S2: {Initial IsAction}

S3: {Initial Inc Date}

Relevant three references in Reference Manager:

Carson Penticuff

Try including the "search type" parameter in your match function to specify exact matches only:

=INDEX({Initial Inc Date}, MATCH([Action ID REF]@row, {Initial ID Ref}, 0))

For the optional search_type argument:

1: (The default value) Finds the largest value less than or equal to search_value (requires that the range be sorted in ascending order)

0: Finds the first exact match (no sort order is required)

-1: Finds the smallest value greater than or equal to search_value (requires that the range be sorted in descending order)

Carson Penticuff

Try including the "search type" parameter in your match function to specify exact matches only:

=INDEX({Initial Inc Date}, MATCH([Action ID REF]@row, {Initial ID Ref}, 0))

For the optional search_type argument:

1: (The default value) Finds the largest value less than or equal to search_value (requires that the range be sorted in ascending order)

0: Finds the first exact match (no sort order is required)

-1: Finds the smallest value greater than or equal to search_value (requires that the range be sorted in descending order)

DWalkerUK

Sorted, thank you so much Carson!

Dan

Carson Penticuff

Awesome, I'm glad it worked!