VBA code for index match if multiple criteria are met

Atlantis764

New Member
Joined
Jan 10, 2022
Messages
9
Office Version
  1. 2019
Platform
  1. Windows
Hi all,
First of all I want to say that I am new to VBA code and I need your help.
I need to create a report for the hours worked by some experts on different projects. There are 100 experts that are working in 5 project. Each expert is working in every project.
I have a excel file with 4 sheets.
In the first sheet (named Home) I have a User Form with the following fields: Date, Employee Name (dropdown list), Project No (dropdown list) and No of hours worked.
When I save a new entry this is visible in a List Box in the Form and it is also automatically entered (new row for each new entry) in the second sheet (named Database). There are 4 columns here (from A to D) identically with the fields from the User Form.
The third sheet (named Hours) has 3 columns: Employee Name, Project No and No of hours allocated for each project.
The last sheet (Support) contain the dates for the dropdown lists that are in the User Form.
For the report I need to add in the second sheet (Database) on column E the No of hours allocated for each new entry. I used the excel formula INDEX-MATCH for each row (and copied down to row 10000) and the excel file is working slow because I already have 4000 rows. Because the formula is copied down when I have a new entry in the User Form (and automatically in the Database sheet) column E is automatically filled with the No of hours allocated.
Is there a VBA code to replace (or to avoid) using the Index-Match formula which makes the excel work slow?
Thanks in advance for your help!
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

bebo021999

Well-known Member
Joined
Jul 14, 2011
Messages
1,130
Office Version
  1. 2016
Could you share curent code and INDEX formula? also image/snapshot (or XL2BB the best) to have a look to the sheets?
 

Atlantis764

New Member
Joined
Jan 10, 2022
Messages
9
Office Version
  1. 2019
Platform
  1. Windows
Hi,
this is the formula that I'm using right now for each row in column E and it is working:
=IFERROR(INDEX(Hours!C:C:Hours!C:C,MATCH(1,(Hours!A:A:Hours!A:A=Database!D3722)*(Hours!B:B:Hours!B:B=Database!G3722),0)),"")
I have attached a picture with my screen (Database sheet). In my file the index-match is in column K (in my question I eliminated the unnecessary columns trying to be more clear).
Thanks!
 

Attachments

  • Database.jpg
    Database.jpg
    237.3 KB · Views: 13

Atlantis764

New Member
Joined
Jan 10, 2022
Messages
9
Office Version
  1. 2019
Platform
  1. Windows
Hi all,

Any help will be greatly appreciated!

Thanks!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,130
Messages
5,835,569
Members
430,367
Latest member
glastonbury

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top