Need assistance to improve speed in looking up value

Hi all,

I am trying to speed-up my lookup process by employing array. However since I am new in array, I have a lot of difficulties. Normally, I would use application.vlookup to look for a value based on the reference. Now, I am trying to use array..

I have searched for many topic for this, but I have not found the solution.

In my attachment, the file contains four sheets, namely rawdata, base, summary and backlog.

There are two main objectives that I would like to achieve:
1. In sheet rawdata, I would like to fill column Q with Grouping Name which requires me to do double vlookup (when not working with array). First to look for the material group code which can be found from column X sheet rawdata --> then look for the group name based on the material group code and match it with the description as listed in sheet base.

So far, my failed code look like this (I am not even reach the objective of returning material group code)

Sub test

dim lra as long
Dim Col As Long 
Dim r As Range
Dim rws As Long, cols As Long

lra = Worksheets("rawdata").Range("A" & Rows.Count).End(xlUp).Row
Set r = Range("A2:N" & lra&)

Dim iw3m() 'Array name is iw3m, which supposedly to get data from column A to N in sheet rawdata
iw3m = r.Value
rws = UBound(iw3m, 1) 'This is how many rows in the array (& the table)
cols = UBound(iw3m, 2) 'This is how many columns in the array (& the table)

For i = 1 To rws
Next i

'This is to fill column O & P from sheet rawdata
For i = 3 To lra
    Cells(i, 15) = MonthName(Month(iw3m(i - 1, 4)))
    Cells(i, 16) = Year(iw3m(i - 1, 4))
Next i

'Using array for vlookup
Dim r2 As Range
Dim rws2 As Long, cols2 As Long
Set r2 = Range("S2:X" & lrb&)
Dim mm60() 'Array name to hold data from column S to X sheet rawdata

mm60 = r2.Value
rws2 = UBound(mm60, 1)
cols2 = UBound(mm60, 2)

For i = 1 To rws2
Next i
    
'PrintArray mm60, "sheet1", 1, 1

Dim lookupval1() '--> I made this array to hold lookup value (the material group name)
ReDim lookupval1(rws1, 1)

For i = 1 To rws '--> My attempt to get material code, BUT does not work
    If IsError(Application.Match(iw3m(i, 5), Application.Index(mm60, 0, 6)) Then
        lookupval1(i, 1) = "Non ZSPA"
    Else
        lookupval1(i, 1) = Application.Match(iw3m(i, 5), Application.Index(mm60, 0, 6))
    End If
Next i
End Sub

2. The second question is to get the last usage based on material ID which will be shown in sheet summary:
The step are as follows:
a. First I move the data from column P, E and K sheet rawdata to sheet backlog (column A to C)
b. Sorting the data based on the year, in which the latest year is placed on top
c. Then I number the appearance based on column A, B and C in sheet backlog. At first I am using application.countif but then jindon has given a much faster way

HTML Code: 

d. Based on the numbering, I would like to show the latest year when the material was used in sheet summary. This was made through code below. The idea of the code is to look for the first appearance of number 1 for matching year, material id and plant and to return the year which is basically the latest year in which the material was used.

My current code, which is long and tends to make the excel crash:

Sub process2()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'----------------------------------------------------------------------------------------------------------------------------------------
'Variable Declaration & Length of Row or Column
'----------------------------------------------------------------------------------------------------------------------------------------
Dim lra As Long, lrb As Long, lrc As Long
Dim lr1 As Long, lr2 As Long

lra = Worksheets("backlog").Range("A" & Rows.Count).End(xlUp).Row
lr1 = Worksheets("summary").Range("A" & Rows.Count).End(xlUp).Row
'----------------------------------------------------------------------------------------------------------------------------------------

'----------------------------------------------------------------------------------------------------------------------------------------
'Process
'----------------------------------------------------------------------------------------------------------------------------------------
Sheets("backlog").Select
'For i = 1 To lra
'    cells(i, 6) = cells(i
Sheets("summary").Select
For j = 4 To 15
    For i = 3 To lr1
        For k = 1 To lra
            If Worksheets("backlog").Cells(k, 2) = Cells(i, 1) And Worksheets("backlog").Cells(k, 3) = Cells(2, j) Then ' And _
               Worksheets("backlog").Cells(k, 5) = 1 Then
                Cells(i, j) = Worksheets("backlog").Cells(k, 1)
                Exit For
            Else
                Cells(i, j) = "n.a."
            End If
        Next k
    Next i
Next j
            
'----------------------------------------------------------------------------------------------------------------------------------------
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

The data shown in file have been truncated except for sheet backlog. The actual data for sheet rawdata column A are more than 60000 rows and the column S are more than 20000 rows. So I am hoping that someone would shed a light to my problem..

Your assistance is greatly appreciated