Index/Match - max value/date result

[ksigota]

I have tried this formula a NUMBER of different ways and still not able to get the result I'm looking for. I think I've researched it so much that I'm now trying the same things repeatedly and still not getting the answer, so I'll make my first post over...

I'm working with excel 2016, while all my other coworkers are with 2010, so I am a little limited on formulas.

I'm really basically trying to bring up the date the furthest away from today (in column C) based on cells in column A matching cell B - and the information is across sheets, but in the same workbook.

Here's the formula I have now: =IF(ISNA(MATCH(A1,'sheet2'!A:A,0)),"",(INDEX('sheet2'!B:B,(MAX(MATCH(A1,'sheet2'!A:A,0))))))

The IF(ISNA(MATCH(A1,'sheet2'!A:A,0)),"" part makes sure that a1 information is actually in sheet 2.


In sheet1

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]Header[/TD]
[TD]WBS[/TD]
[TD]Last Material Due[/TD]
[TD]How many materials[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0813.02[/TD]
[TD]Formula[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In sheet2:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]Header[/TD]
[TD]WBS[/TD]
[TD]DATE[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0813.02[/TD]
[TD]02/22/19[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0813.02[/TD]
[TD]01/15/19[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]0799.01[/TD]
[TD]02/16/19[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]0813.02[/TD]
[TD]02/03/19[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]0654.02[/TD]
[TD]01/23/19[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]0813.02[/TD]
[TD]01/19/19[/TD]
[/TR]
</tbody>[/TABLE]



I hope all this makes sense. I substituted the actual formula for the tables above to try to give a visual representation. But basically I'm trying to say is if sheet 2 HAS the WBS (sheet 1 a1), then index all the matches in sheet 2 and give me the furthest date from B:B.

Thanks in advance.

 

[Fluff]

How about
=IF(ISNA(MATCH(A2,Sheet2!A2:A7,0)),"",LARGE(IF(Sheet2!A2:A7=A2,Sheet2!B2:B7),1))

 

[ksigota]

With CSE that appears to work! It must be the K value in the LARGE function (a function I've actually never used), as I don't have experience. I did try 0.

One variation to throw in the mix that I didn't mention, because I wasn't sure it mattered...When there ISN'T a date for column B in sheet2, a general date is reported as 0000-00-00.

What I ended up using because of that was =IF(ISNA(MATCH(A2,Sheet2!A2:A7,0)),"",IFERROR(LARGE(IF(Sheet2!A2:A7=A2,Sheet2!B2:B7),1),"No Date")) with CSE and it appears to work perfectly.

Thank you very much!!!

 

[Fluff]

Glad you got it working & thanks for the feedback.

 

[ksigota]

I found a little more items that needed to be factored into this formula.


In sheet1

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]Header[/TD]
[TD]WBS[/TD]
[TD]Last Material Due[/TD]
[TD]How many materials[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0813.02[/TD]
[TD]Formula[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In sheet2:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]Header[/TD]
[TD]WBS[/TD]
[TD]DATE[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0813.02[/TD]
[TD]02/22/19[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0813.02[/TD]
[TD]01/15/19[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]0799.01[/TD]
[TD]02/16/19[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]0813.02[/TD]
[TD]02/03/19[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]0654.02[/TD]
[TD]01/23/19[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]0813.02[/TD]
[TD]01/19/19[/TD]
[/TR]
</tbody>[/TABLE]

(basically table to is giving lead time dates (B) for the parts ordered against the WBS (A) - each line is a different part/purchase req/purchase order)

We ended with the array function =IF(ISNA(MATCH(A2,Sheet2!A2:A7,0)),"",IFERROR(LARGE(IF(Sheet2!A2:A7=A2,Sheet2!B2:B7),1),"No Date")) that appeared to work great. What I didn't think to factor in was if there was a purchase req that did not yet have a date in sheet 2 assigned to it. The other thing that throws that for a loop is sometimes there are expedite fees that show the 000-00-00 date as well.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]Header[/TD]
[TD]WBS[/TD]
[TD]DATE[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0813.02[/TD]
[TD]02/22/19[/TD]
[TD]Pin[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0813.02[/TD]
[TD]000-00-00[/TD]
[TD]Block[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]0799.01[/TD]
[TD]02/16/19[/TD]
[TD]Sheet[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]0813.02[/TD]
[TD]000-00-00[/TD]
[TD]Expedite Fee[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]0654.02[/TD]
[TD]01/23/19[/TD]
[TD]Gasket[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]0813.02[/TD]
[TD]01/19/19
[/TD]
[TD]Terminal[/TD]
[/TR]
</tbody>[/TABLE]

What I need to figure out is how to show when a part (C) doesn't have a date (B) without showing the parts that have "expedite" in the nomenclature. It is possible to just delete the expedite fees when importing the data to sheet 2.

I'm just starting to work on this part of the problem, but thought I would post up in case someone had a thought or a direction.

TIA