Returning a ship date from another sheet based on matching item and order number?

Natalie McMorrow · 06/11/20

I am trying to find a formula to pull a ship date from another sheet if two criteria match.

Here's a representation of the data in the main sheet and in the ship date sheet:

So if both order# and part number match, the formula would insert the ship date from the other sheet.

Is this doable? I've messed around with IF, INDEX, COLLECT, JOIN, VLOOKUP but not having much luck.

Also due to the cell limit I've had to split the ship dates into two sheets (ship dates 1 & ship dates 2), but the main sheet may need to look up the data from either sheet. Is it possible to reference two sheets in one formula?

I've tried searching help and the forums - any assistance would be greatly appreciated!

Werner Gerstacker · 06/11/20

Hi @Natalie McMorrow ,

I haven't tried it in Smartsheet yet, but I've done it in Excel with a SUMIFS - as long as each Order#/Part Number line only has one ship date that should work fine. At least it does in Excel because dates are simply stored as numbers there.

Maybe you can handle the split between two sheets by 'adding' two SUMIFS together, the sheet that doesn't have the right reference should just return a 0 so nothing is added to the result form the other sheet. If the formula for the sheet without the correct reference returns an error just wrap both SUMIFS parts separately into IFERROR functions.

-Werner

Natalie McMorrow · 06/15/20

https://community.smartsheet.com/discussion/comment/253244#Comment_253244

Hi Werner,

Thank you for the response! I'm not entirely sure how to do what you kindly suggested - could you please give an example of a working formula?

- Natalie

Werner Gerstacker · 06/15/20

Hi @Natalie McMorrow ,

my idea was to do something like this:

The formula is:

=SUMIFS([Sheet 1 - Ship Date]:[Sheet 1 - Ship Date], [Sheet1 - Order Number]:[Sheet1 - Order Number], [Order Number]@row, [Sheet1 - Part Number]:[Sheet1 - Part Number], [Part Number]@row) + SUMIFS([Sheet 2 - Ship Date]:[Sheet 2 - Ship Date], [Sheet1 - Order Number]:[Sheet1 - Order Number], [Order Number]@row, [Sheet1 - Part Number]:[Sheet1 - Part Number], [Part Number]@row)

But while this works fine in Excel, it does not in Smartsheet.

It seems to have to do with the way dates are handled in Smartsheet - at works fine for numbers, of course.

Maybe somebody else has an idea how the use a SUMIF with dates.

Other than that, I think a COLLECT function might work but I'm not really a specialist there.

Paul Newcome · 06/15/20

You could try something like this...

=IF(COUNTIFS({Date Sheet 1 Order Number}, [Order#]@row, {Date Sheet 1 Part Number}, [Part Number]@row) > 0, INDEX(COLLECT({Date Sheet 1 Ship Date}, {Date Sheet 1 Order Number}, [Order#]@row, {Date Sheet 1 Part Number}, [Part Number]@row), 1), INDEX(COLLECT({Date Sheet 2 Ship Date}, {Date Sheet 2 Order Number}, [Order#]@row, {Date Sheet 2 Part Number}, [Part Number]@row), 1)

Basically we have an INDEX/COLLECT to pull the matching ship date from Date Sheet #1. We then replicate it for Date Sheet #2.

Then we use a COUNTIFS to see if there is a matching row in Date Sheet #1 and drop that in an IF statement to say that if the count is greater than zero, pull from Date Sheet #1, otherwise pull from Date Sheet #2.

=IF(count on sheet 1> 0, pull from sheet 1, pull from sheet 2)

Werner Gerstacker · 06/15/20

Just thought about a trick I used to use for Excel - adding a helper column with the combined values you're looking for in a VLOOKUP:

=IFERROR(VLOOKUP([O&P]@row, [Sheet1 - Helper]:[Sheet1 - Ship Date], 2, false), IFERROR(VLOOKUP([O&P]@row, [Sheet2 - Helper]:[Sheet2 - Ship Date], 2, false), ""))

The Helper columns O&P, Sheet1 - Helper and Sheet2 - Helper are set up with this kind of formula:

=[Order Number]@row + " - " + [Part Number]@row

Paul Newcome · 06/15/20

@Werner Gerstacker You could also use an INDEX/COLLECT to avoid needing the helper columns.

Werner Gerstacker · 06/15/20

I have used the collect function before - but always have trouble to wrap my head around it.

And from what I've seen in the forum here, I am by far not the only one 😉

As VLOOKUP is a much easier concept to wrap one's brain around I'm just offering an alternative approach.

Paul Newcome · 06/15/20

@Werner Gerstacker No worries. It took me a while to get a feel for it. The best way I can explain it is that you enter the range you want to collect FIRST then alternate criteria range 1, criteria 1, criteria range 2, criteria 2, criteria range 3, criteria 3, ..............................

Once I got the hang of the COLLECT function, so many new possibilities presented themselves. It is kind of like adding an "IFS" to a function.

Want to JOIN cells that meet specific criteria but we don't have a JOINIFS?

=JOIN(COLLECT({range to join}, {Criteria range 1}, criteria 1, {Criteria range 2}, criteria 2), "delimiter")

Want to pull the most recent date from a range but only if it meets certain criteria?

=MAX(COLLECT({date column to run MAX function on}, {Criteria range 1}, criteria 1, {Criteria range 2}, criteria 2))

I must say though... I do like your IFERROR/IFERROR combo. That could probably be used on my solution instead of the IF/COUNTIFS. I hadn't thought of something like that before.

=IFERROR(INDEX(COLLECT({Date Sheet 1 Ship Date}, {Date Sheet 1 Order Number}, [Order#]@row, {Date Sheet 1 Part Number}, [Part Number]@row), 1), INDEX(COLLECT({Date Sheet 2 Ship Date}, {Date Sheet 2 Order Number}, [Order#]@row, {Date Sheet 2 Part Number}, [Part Number]@row), 1))

or

=IFERROR(INDEX(COLLECT({Date Sheet 1 Ship Date}, {Date Sheet 1 Order Number}, [Order#]@row, {Date Sheet 1 Part Number}, [Part Number]@row), 1), IFERROR(INDEX(COLLECT({Date Sheet 2 Ship Date}, {Date Sheet 2 Order Number}, [Order#]@row, {Date Sheet 2 Part Number}, [Part Number]@row), 1), ""))

Returning a ship date from another sheet based on matching item and order number?

Answers

Help Article Resources

Categories