Need assistance to improve speed in looking up value

Hi xladept,

Thanks for your response, however, both of your codes do not give me the result that I expect.

>> For my first question: your code simply take the material group from column X and then look for the material group description from sheet base column B. Therefore, it does not match with the material ID in column E.
The condition of my real data as follow:
1. The material ID is not sorted (because the data is retrieved directly from the database)
2. The number of row in column A sheet rawdata can be much higher than in column S

Using the code that I know (vlookup), the step I make are:
1. Since material ID is not sorted, first I look for the material group using column S to AG as table array. The lookup value is material ID from column A.
2. Once I get the correct material group, then I do the second lookup with column A to B in sheet base as table array. The lookup value is the material group found using the first lookup
Following that step, therefore my original code looks like:

    If IsError(Application.VLookup(Cells(i, 5), Range("S3:AG" & lrb&), 15, 0)) Then
        Cells(i, 17) = "Non ZSPA"
    Else
        MatGrp = Application.VLookup(Cells(i, 5), Range("S3:AG" & lrb&), 15, 0)
        Cells(i, 17) = Application.VLookup(Cells(i, 5), Range("S3:AG" & lrb&), 15, 0)
    End If

in my actual data, I first doing vlookup for the material group listed in column S (the table array for the vlookup function is column A & B from sheet base) and the result is listed in column AG sheet rawdata.

However, executing that syntax took a lot of time. Therefore, I am asking in this forum, since I cannot solve it (as you can see in my first post of this thread)

>> For my second question:
Objective is to know the time when a material is used (shown by the year of its last usage).
In order to do so, the method that I have in mind are:
1. First I prepare a table containing material ID in column A and Plant ID in row 2 sheet summary
2. I prepare before hand sheet backlog to be filled with data from column E, K and P sheet rawdata
3. I sort the year in descending order, therefore ensuring that the latest usage will be placed on top for each material ID and its respective plant ID
4. I numbering that order of appearance, first my own code using countifs method. But then Jindon helped and provided code to make it faster
5. The reason for doing step 4, is to ensure that number 1 always correlate with the latest usage of a particular material ID and its respective plant
6. Then, to complete the sheet summary, I use the following code:

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
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

The code function was meant to to look for number 1 (column D) by going through all the data in sheet backlog which has the matching material ID from each row in column A sheet summary and matching plant from each column in row 3 sheet summary. However the above code is too slow and causing excel to crash. Therefore, again, I am trying to seek assistance from this forum.

I hope that my explanation has provided more detail of what I would like to achieve without using the code that I familiar with (because the process time is very long and tend to crash the excel).