Nested formula

Pamela.Baron

I have a schedule sheet and a production sheet, I am trying to pull the pick up date from the schedule sheet to the production sheet based on an Order Number, which on the Schedule sheet can be surrounded by other information.

Schedule sheet:

Pick Up Date field is a date field.

Production sheet:

I have tried:

=IF(CONTAINS([number-order]@row, {WDC SHIPPINGSCHEDULE Order number/contents}),

"pudate", "false") but I don't know how to replace "pudate" with the actual date on the Schedule sheet.

KPH

You are thinking along the right lines with the CONTAINS @Pamela.Baron and have successfully matched on a partial string.

But the IF or nested IF is not going to get you what you need. You need an INDEX function to scan the list in your schedule sheet and return the row that meets the criteria. IF will not scan through the list - it is more of an absolute choice - IF true do this, if not do something else (you would need a lot of something else's to achieved what you want). INDEX is more efficient.

We can combine INDEX and COLLECT and CONTAINS like this.

=INDEX(COLLECT({WDC SHIPPINGSCHEDULE Pick Up Date}, {WDC SHIPPINGSCHEDULE Order number/contents}, CONTAINS([Number-Order]@row, @cell)), 1)

This COLLECTS the value in the column I have called "WDC SHIPPINGSCHEDULE Pick Up Date" (Pick Up Date in your Schedule sheet), where the Number/Contents column in that sheet (the reference you have already set up) CONTAINS the number-order of the row in the current sheet. It returns the 1st row that matches.

Hope that helps.