VBA - VLOOKUP With Multiple Parameters

Question also posted here:


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

Summary Sheet
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.

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

Open in new window

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"

Sample workbook attached.
Eitel DagninIT Security AdministratorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
There's no code in the workbook to check, but you don't nee VBA to autofill  formulas, simply set up the data as a Table and add the formulas manually at first, but then as rows are added the formulas will populate down, see Calculated Columns in the article I have posted a link to.. You actually have a Table on one sheet

Overview of Excel tables

You can actually set this up without using Tables, but Tables are the best way.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may give this a try...

In the attached, click the button called "Place Formula" on the Main sheet to run the 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

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Eitel DagninIT Security AdministratorAuthor Commented:
@Roy - Thank you for the reply, I have not yet looked into what you suggested, but I will for future :)

@Neeraj - Thank you very much for the code and the help, it works brilliantly :)
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Eitel DagninIT Security AdministratorAuthor Commented:
Code does perfect vlookup and inserts match into correct cells when a match is found.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Eitel! Glad it worked as desired.
Roy CoxGroup Finance ManagerCommented:
As I said, there is no need to use VBA for this, it really is worth  getting into Tables.

Even without Tables and back to 2003 you can set this option up in Excel Options.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.