Index/Collect/Lookup not working

LFoster

I've put together a function that pulls an autonumber value from a cross-reference. The problem is that the formula does not evaluate on all records. In the first snippet below the Row Record field does not return a value for all lookup values in the Base PO field even though I have confirmed that all the lookup values exist in the reference sheet. On top of that, if I enter a new record in the reference sheet with a new dummy Base PO the 2 records that originally returned a value in the Row Record field in the first snippet now do not. I can't understand why the formula either works for all or none. The only problem field for me is the Row Record field as all other error fields lookup that value.

Formula:

=IF([Requested POs]@row = "", "", IF([Next Appointment Date]@row = "", "", INDEX(COLLECT({108 Inbound Detail Grid - Row Record}, {108 Inbound Detail Grid - PO No Suffix}, CONTAINS([Base PO]@row, @cell), {108 Inbound Detail Grid - Status}, OR(@cell = "Scheduled", @cell = "Unassigned", @cell = "Unloading"), {108 Inbound Detail Grid - App Date}, [Next Appointment Date]@row, {108 Inbound Detail Grid - Appointment Time Decimal}, @cell = MIN({108 Inbound Detail Grid - Appointment Time Decimal})), MATCH([Next Appointment Date]@row, {108 Inbound Detail Grid - App Date}, 0))))

Paul Newcome

I feel like it may be how we are pulling in the MIN value for the decimal.

=IF([Requested POs]@row = "", "", IF([Next Appointment Date]@row = "", "", INDEX(COLLECT({108 Inbound Detail Grid - Row Record}, {108 Inbound Detail Grid - PO No Suffix}, CONTAINS([Base PO]@row, @cell), {108 Inbound Detail Grid - Status}, OR(@cell = "Scheduled", @cell = "Unassigned", @cell = "Unloading"), {108 Inbound Detail Grid - App Date}, [Next Appointment Date]@row, {108 Inbound Detail Grid - Appointment Time Decimal}, @cell = MIN({108 Inbound Detail Grid - Appointment Time Decimal})), 1)))

You are pulling in the smallest decimal value, but the MIN is not also being filtered by the same range/criteria sets. I have run into issues before putting a COLLECT inside of a COLLECT, so let's try moving the MIN function into its own column.

Helper column on formula sheet:

=MIN(COLLECT({108 Inbound Detail Grid - Appointment Time Decimal}, {108 Inbound Detail Grid - PO No Suffix}, CONTAINS([Base PO]@row, @cell), {108 Inbound Detail Grid - Status}, OR(@cell = "Scheduled", @cell = "Unassigned", @cell = "Unloading"), {108 Inbound Detail Grid - App Date}, [Next Appointment Date]@row))

Then adjust the main formula like so:

=IF([Requested POs]@row = "", "", IF([Next Appointment Date]@row = "", "", INDEX(COLLECT({108 Inbound Detail Grid - Row Record}, {108 Inbound Detail Grid - PO No Suffix}, CONTAINS([Base PO]@row, @cell), {108 Inbound Detail Grid - Status}, OR(@cell = "Scheduled", @cell = "Unassigned", @cell = "Unloading"), {108 Inbound Detail Grid - App Date}, [Next Appointment Date]@row, {108 Inbound Detail Grid - Appointment Time Decimal}, @cell = [Helper Column]@row), 1)))

Paul Newcome

Instead of using INDEX/COLLECT/MATCH, just use INDEX/COLLECT and use the number 1 for the row reference portion of the INDEX function.

=INDEX(COLLECT(......................), 1)

LFoster

@Paul Newcome That's the formula I originally used before switching to match. When I make that change I will get a value for all of the dummy lookup values I have but nothing for a true PO value (one that starts with the number 1).

=IF([Requested POs]@row = "", "", IF([Next Appointment Date]@row = "", "", INDEX(COLLECT({108 Inbound Detail Grid - Row Record}, {108 Inbound Detail Grid - PO No Suffix}, CONTAINS([Base PO]@row, @cell), {108 Inbound Detail Grid - Status}, OR(@cell = "Scheduled", @cell = "Unassigned", @cell = "Unloading"), {108 Inbound Detail Grid - App Date}, [Next Appointment Date]@row, {108 Inbound Detail Grid - Appointment Time Decimal}, @cell = MIN({108 Inbound Detail Grid - Appointment Time Decimal})), 1)))

Paul Newcome

Are you able to show the source data?

LFoster

Here is a sample with the fields from the formula.

Paul Newcome

What are the formulas in the No Suffix and the Appt Time Decimal columns?

LFoster

No Suffix:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([PO Number(s)]@row, "-00", ""), "-01", ""), "-02", ""), "-03", ""), "-04", ""), "-05", ""), "-06", ""), "-07", ""), "-08", ""), "-09", ""), "-10", ""), "-11", ""), "-12", ""), "-13", ""), "-14", ""), "-15", ""), "-16", ""), "-17", ""), "-18", ""), "-19", ""), "-20", "")

Appt Time Decimal:

=IFERROR(VALUE(LEFT([Appointment Time]@row, FIND(":", [Appointment Time]@row) - 1)) + VALUE(RIGHT([Appointment Time]@row, 2)) / 60, "")

Paul Newcome

I feel like it may be how we are pulling in the MIN value for the decimal.

=IF([Requested POs]@row = "", "", IF([Next Appointment Date]@row = "", "", INDEX(COLLECT({108 Inbound Detail Grid - Row Record}, {108 Inbound Detail Grid - PO No Suffix}, CONTAINS([Base PO]@row, @cell), {108 Inbound Detail Grid - Status}, OR(@cell = "Scheduled", @cell = "Unassigned", @cell = "Unloading"), {108 Inbound Detail Grid - App Date}, [Next Appointment Date]@row, {108 Inbound Detail Grid - Appointment Time Decimal}, @cell = MIN({108 Inbound Detail Grid - Appointment Time Decimal})), 1)))

You are pulling in the smallest decimal value, but the MIN is not also being filtered by the same range/criteria sets. I have run into issues before putting a COLLECT inside of a COLLECT, so let's try moving the MIN function into its own column.

Helper column on formula sheet:

=MIN(COLLECT({108 Inbound Detail Grid - Appointment Time Decimal}, {108 Inbound Detail Grid - PO No Suffix}, CONTAINS([Base PO]@row, @cell), {108 Inbound Detail Grid - Status}, OR(@cell = "Scheduled", @cell = "Unassigned", @cell = "Unloading"), {108 Inbound Detail Grid - App Date}, [Next Appointment Date]@row))

Then adjust the main formula like so:

=IF([Requested POs]@row = "", "", IF([Next Appointment Date]@row = "", "", INDEX(COLLECT({108 Inbound Detail Grid - Row Record}, {108 Inbound Detail Grid - PO No Suffix}, CONTAINS([Base PO]@row, @cell), {108 Inbound Detail Grid - Status}, OR(@cell = "Scheduled", @cell = "Unassigned", @cell = "Unloading"), {108 Inbound Detail Grid - App Date}, [Next Appointment Date]@row, {108 Inbound Detail Grid - Appointment Time Decimal}, @cell = [Helper Column]@row), 1)))

LFoster

It works, thank you!

Paul Newcome

Happy to help. 👍️