excel - VBA - Match Lookup With Multiple Parameters

Question

I have a few macros that perform various tasks, and it seems rather pointless that the final step of the process requires the user to manually enter the formula, so here is the scenario:

I have 2 spreadsheets:

1. Consolidated Sheet - Contains an employee number and the employee details (See image below)

   

2. Summary Sheet - Contains only 3 columns; AppName, EmpNum, Status (See image below)

   Note: There is currently no data on any of these sheets, and on the summary sheet, I create a table from the data so that the formula will work

   

On the consolidated sheet at the end of the column "Supervisor Email", there are a few more columns containing the application names (See image below)

Originally, the idea was to lookup the employee number and app name with the below formula, then copy the status to the relevant cell - but this formula does not seem to be working AND it requires the user to insert the formula manually into each cell under every application name.

Formula:

=IFERROR(INDEX(Table2[Status],SUMPRODUCT((Table2[App Name]=L$1)*(Table2[Employee Number]=$A2)*(ROW(Table2[Status])))-1,1),"")

Now, what I am looking to do is relatively the same thing, but with 2 differences:

1. I don't want the status to show in the cell the formula is placed into - If there's a match, then it should display "MATCH"

   and if there is no match then the cell should be blank

2. I need this formula to be in a macro that finds the "lastRow" and populates across the columns from App1 - to the last AppName and down to the "lastRow"

Answer 1

I got this question answered here by user Subodh Tiwari (Neeraj):

https://www.experts-exchange.com/questions/29098511/VBA-VLOOKUP-With-Multiple-Parameters.html#acceptAnswerByMember

Sample workbook is attached with the post in this link.

Here is the complete code:

Sub PlaceFormula()
Dim ws As Worksheet
Dim lr As Long
Dim lc As Long

With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
    .EnableEvents = False
End With

Set ws = Sheets("Main")
lr = ws.Cells(Rows.Count, 1).End(xlUp).Row
lc = ws.Cells(1, Columns.Count).End(xlToLeft).Column

ws.Range("B2", ws.Cells(lr, lc)).Formula = "=IF(ISNUMBER(MATCH($A2&B$1,INDEX(tStatus[[Employee Number]:[Employee Number]]&tStatus[[Wk Number]:[Wk Number]],),0)),""Match"","""")"
ws.Range("B2", ws.Cells(lr, lc)).Value = ws.Range("B2", ws.Cells(lr, lc)).Value

With Application
    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
    .EnableEvents = True
End With
End Sub