multisheet lookup and sales calculation

Another approach.

With the macro below, and after that a pivot table of your data.

See the attached file.

Please reply if this is what you're up to.

Sub integratie_Oeldere_revisted_vs2()

Dim wsTest As Worksheet

'check if sheet "Consolidated" already exist

Const strSheetName As String = "Consolidated"
 
Set wsTest = Nothing
On Error Resume Next
Set wsTest = ActiveWorkbook.Worksheets(strSheetName)
On Error GoTo 0
 
If wsTest Is Nothing Then
    Worksheets.Add.Name = strSheetName
End If

With Sheets("Consolidated")
    .UsedRange.ClearContents
    .Range("A1:d1").Value = Array("sheet", "Sl. No.", "Name", "Sales")
    For Each sh In Sheets
        With sh
            If .Name <> "Consolidated" And .Name <> "Pivot table" Then
                Rng = .Cells.Find("*", , , , xlByRows, xlPrevious).Row - 1
                NR = Sheets("Consolidated").Cells.Find("*", , , , xlByRows, xlPrevious).Row + 1
                If Rng > 0 Then
                    Sheets("Consolidated").Cells(NR, 1).Resize(Rng) = .Name
                    Sheets("Consolidated").Cells(NR, 2).Resize(Rng, 3) = .Range("A2").Resize(Rng, 3).Value
                End If
            End If
        End With
    Next
    
    .Columns("A:Z").EntireColumn.AutoFit
End With
End Sub

with use of vba it is simple but i am not using vba.

Why not?

You said in #3

with use of vba it is simple

If it is that simple, why not use it.

If it is that simple, why use the formula (which is difficult for you, otherwise you don't asked the question).


Probably it's on me, but I don't get it.