• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 85
  • Last Modified:

VBA - VLOOKUP With Multiple Parameters

Question also posted here:

https://stackoverflow.com/questions/50227693/vba-vlookup-with-multiple-parameters

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)

EmpDetails
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)

Apps
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.
tt-Match_Lookup_and_Copy_Column.xlsx
0
Eitel Dagnin
Asked:
Eitel Dagnin
  • 2
  • 2
  • 2
1 Solution
 
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.
0
 
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

tt-Match_Lookup_and_Copy_Column.xlsm
0
 
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 :)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Eitel DagninIT Security AdministratorAuthor Commented:
Code does perfect vlookup and inserts match into correct cells when a match is found.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Eitel! Glad it worked as desired.
0
 
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.

Options.png
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now