Sumifs/Index/Match----Please fix

You need to explain the problem PROPERLY.

=IFERROR(INDEX('Lev1'!$B$8:$N$13,MATCH($A2,'Lev1'!$B$8:$B$13,0),MATCH($B$1,'Lev1'!$B$8:$N$8,0)),"NA")
Try the above in "B2" Cell of Results sheet and copy towards down

So Result!B1 is the category number you are trying to find?

in Result!B2
=INDEX('Lev1'!C$9:N$13,MATCH(A2,'Lev1'!B$9:B$13,0),MATCH(B$1,'Lev1'!C$8:N$8,0))
and copy down the column

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

See the attached file.

Sub CONVERTROWSTOCOL_Oeldere_revisted()

Dim rsht1 As Long, rsht2 As Long, i As Long, col As Long, wsTest As Worksheet

'check if sheet "ouput" already exist

Const strSheetName As String = "Output"
 
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("Output")
    .UsedRange.ClearContents
    .Range("A1:C1").Value = Array("Cat", "Month", "Value")

    End With
    
    
    rsht1 = Sheets("Lev1").Range("B" & Rows.Count).End(xlUp).Row
    rsht2 = Sheets("Output").Range("A" & Rows.Count).End(xlUp).Row
    col = 3
    
    For i = 9 To rsht1
            Do While Sheets("Lev1").Cells(8, col).Value <> ""
            rsht2 = rsht2 + 1
           
            Sheets("Output").Range("A" & rsht2).Value = Sheets("Lev1").Range("B" & i).Value
           
            Sheets("Output").Range("B" & rsht2).Value = Sheets("Lev1").Cells(8, col).Value
            
            Sheets("Output").Range("C" & rsht2).Value = Sheets("Lev1").Cells(i, col).Value
            
            
     
            col = col + 1
        Loop
        col = 3
    Next
  With Sheets("Output")
  
   Columns("A:Z").EntireColumn.AutoFit
    End With
End Sub

Try

Formula:

=HLOOKUP(B$1,'Lev1'!$C$8:$N$13,MATCH($A2,'Lev1'!$B$8:$B$13,0))

Originally Posted by boopathiraja

Try

Formula:

=HLOOKUP(B$1,'Lev1'!$C$8:$N$13,MATCH($A2,'Lev1'!$B$8:$B$13,0))

nice formula

Thanks Siva

Originally Posted by nflsales

nice formula