Need to return all values, using index & Match

[winstela]

Good Morning,

I have 2 issues that I need help with

1.

I am trying to create a template that has headers in row 1 and formula in row 2 columns A to G
then when I paste data into the worksheet, the formula will copy down.

In Col F I am using the formula below to lookup the manufacture date against a sales order line.

Sometimes the manufacturing date has to be spread out over several days, how do I change the formula below to get the latest date.

Im looking up the sales order in Column A and matching that with the Col A on the MFG tab to return the value of Col J on the MFG

=IFERROR(INDEX(MFG!J:J,MATCH(A2,MFG!A:A,0)),"-")

2.

I am also using a worksheet change event to copy fil down the formula when changes are made to col H, if the formula has to be entered as an array, will the code below work

Private Sub Worksheet_Change(ByVal Target As Range)





If Target.Columns.CountLarge > 55 Then
Exit Sub
End If
Application.ScreenUpdating = False
If Not Intersect(Target, Columns("H")) Is Nothing Then  
Range("A2:G2").AutoFill Destination:=Range("A2:G" & Cells(Rows.Count, "H").End(xlUp).Row)


End If


Application.ScreenUpdating = True


End Sub

All help appreciated
Thanks

 

[winstela]

Hi I have manged to sort using

{[TABLE="width: 94"]
<tbody>[TR]
  [TD="class: xl63, width: 94"]=IFERROR(INDEX(MFG!J:J,MATCH(A2,MFG!A:A,0)+COUNTIF(MFG!A:A,A2)-1),"-")}[/TD]
[/TR]
</tbody>[/TABLE]

and the vba code works fine

Thanks

 

[winstela]

The curly bracket should be infront of the = sign