How to use VBA to perform Index/MATCH for multiple Criteria as well as ignore blank cells

Hi,
I need a big help here
I'm required to complete a report which need to meet multiple criteria by using macro scripts.
This report previously only require to match one criteria. However, now it need to read multiple criteria in the range that contains more than one criteria. For example if Column C contains supplier name - Kelly with another criteria in Column E3 = return IFSBilling as a result else if just only contains Kelly return "Contingent Workers" as result.
There are 2 additional column to vlookups as well.
Can anyone help me here? seriously i need a solution now. Let me know if more clarification needed.

Many thanks in advance

VBA name : LookupValues

The GLAP result Column AX is where the result is returning by reading the data from.

Dim colwithTextSAP As Integer 'will store the column number of the text to be checked in the SAP data sheet
Dim colToPlaceFoundValue As Integer 'will store the column number where the value found will be place
Dim colwithTextLookup As Integer 'will store the column number of the text to be looked for in the lookup sheet
Dim firstRowSAP As Integer 'will store first data row in SAP data file
Dim lastRowSAP As Integer 'will store last row in SAP data file
Dim lastRowLookupVar As Integer 'will store last row in lookupValuesSheetName
Dim GLAPSheetName As String 'will store SAP Data sheet name
Dim lookupValuesSheetName As String 'will store the sheet name which has the lookup values
Dim firstRowLookupSheet As Integer 'will store first row with data in the lookup sheet
Dim textToSearchIn As String 'string to search in SAP file
Dim textToSearchInLookupValue As String 'string that we would like to check if exists in the textToSearchIn
Dim valueFoundToUpdateField As String 'once search string found, this variant will store the value that was found to be placed in the category field in the SAP data sheet
Dim colCategoryInLookupValueSheet As Integer ' column number for category column number in the lookup value sheet
Dim sapCurrRow, lookupCurrRow As Integer 'current row to be used for SAP and Lookup loops
Dim mypost As Variant


GLAPSheetName = "GLAP"
lookupValuesSheetName = "LookupValues"


'store variants for lookupValuesSheetName
Sheets(lookupValuesSheetName).Select
firstRowLookupSheet = 3 'first row with data in the lookup sheet
colwithTextLookup = 4 'column number with text to search for in the lookup sheet
colCategoryInLookupValueSheet = 6 ' category column number in the lookup value sheet
'find last row in lookupValuesSheetName
Range("F1").Select
Selection.End(xlDown).Select
lastRowLookupVar = Selection.Row


'store variants for GLAPSheetName
Sheets(GLAPSheetName).Select
firstRowSAP = 3 'first row with data in GLAP data sheet
colwithTextSAP = 18 'column number of text to be checked
colToPlaceFoundValue = 50 'column number to place the found value
'find last row in SAP data file
Range("AQ2").Select
Selection.End(xlDown).Select
lastRowSAP = Selection.Row


'outer loop, review each line in SAP data sheet
For sapCurrRow = firstRowSAP To lastRowSAP
Sheets(GLAPSheetName).Select 'get value to look in from SAP sheet name
textToSearchIn = Cells(sapCurrRow, colwithTextSAP)
If Len(textToSearchIn) > 0 Then 'if textToSearchIn is not blank, will search for match in lookupvalue sheet
'Inner loop, until find a match search in the lookup values sheet for a match in order to add the category
Sheets(lookupValuesSheetName).Select 'search in lookup values sheet
For lookupCurrRow = firstRowLookupSheet To lastRowLookupVar
textToSearchInLookupValue = Cells(lookupCurrRow, colwithTextLookup)

Mypos = InStr(1, textToSearchIn, textToSearchInLookupValue)
If (Mypos <> 0) And (Len(textToSearchInLookupValue) <> 0) Then 'if found the subsrting and the substring is not blank

valueFoundToUpdateField = Cells(lookupCurrRow, colCategoryInLookupValueSheet).Value 'get the value to place in the Category column
lookupCurrRow = lastRowLookupVar + 1 'if found exit inner loop to move to next row in SAP data file
Sheets(GLAPSheetName).Select 'get value to look in from SAP sheet name
Cells(sapCurrRow, colToPlaceFoundValue).Value = valueFoundToUpdateField 'place the found value in the SAP sheet
End If
'if not found (meaning Mypos=0) then inner loop will continue

Next lookupCurrRow
lookupCurrRow = 0 'for next run of innter loop
End If 'If length(textToSearchIn)>0

Next sapCurrRow 'exit outer loop to review each line in SAP data sheet