Index/Collect/Lookup not working
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))))
Best Answer
-
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)))
Answers
-
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)
-
@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)))
-
-
Here is a sample with the fields from the formula.
-
What are the formulas in the No Suffix and the Appt Time Decimal columns?
-
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, "")
-
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)))
-
It works, thank you!
Help Article Resources
Categories
Check out the Formula Handbook template!