I'd like to create a function that would take criteria from Lookup_ref sheet and return a category name on Data sheet, column H. Something like:
Function ReturnCategory() As String ' Dim tbl As ListObject ' Set tbl = Sheets("lookup_ref").ListObjects(1) If ITEM1 = "CD36917" And SUBGROUP Like "09*" And LINE_CODE Like "*N8WV1*" And GROUP1 = "555" Then ReturnCategory = "ABC" ElseIf ITEM1 = "KM39352" And LINE_CODE Like "*XY00000ZS36W4*" And GROUP1 = "555" Then ReturnCategory = "DEF" ElseIf LINE_CODE Like "*N8WV1*" And GROUP1 = "555" Then ReturnCategory = "JKL" '..... End If End Function
Custom categories would be created from populated fields and would take into account the operator equals, contains .. right below the header.
I thoght of making UDF worksheetfunction, where loop creates a string of nested IFs for each row combination but I'd probably hit the nesting limit. On the other hand,
I am not sure how to create a VBA IF statement of variable lenght.