How to macro multiple lookups on over 500,000 rows of data efficiently?

Hi Excel Forums.

I am posting again as my previous post did not provide enough information for people to be able to help. I have now made a dummy spreadsheet with some data added in to explain my problem a bit better.


In the first sheet, the data that is filled out is received from a report that is auto generated. I want to fill out the remaining 3 cells using a Macro and information on the other sheets. The self-approval column should say yes if the employee's band allows them to approve the invoice amount (found in sheet 2). It should say no if they are not allowed to approve themselves, in which case the next cell should populate with the details of their manager (found in sheet 3). If that manager cannot approve due to their band being lower, then it follows onto their manager and so on until the correct approval party is there. This file has 350,000 rows of data.


Sheet 2 shows the proposed amounts each band can approve. These limits will be changed and altered to see which method provides the best coverage.


Sheet 3 shows employee details extracted from a database. Has over 25 fields of information but cut down to show the ones I will be using. This file has 402,000 rows of data.


I have currently toyed with a Macro, where the self-approval populates based on variables stored from Sheet 2 into the Macro and then inputs yes/no into the self-approval cell. That works fine. The only roadblock I am facing is when I use too many if's to check for a managers band code, which results in the macro looping again and again for all the rows of data. What is the most efficient way to do this, please?

Use PQ(Power Query). Easy and could handle massive data.

Thank you so much for your response. I've just had a go at trying it, but I'm unsure about how to use it to get my solution. So far I've loaded the different sheets into a query.

Just watch the followings clips:

https://www.youtube.com/watch?v=73T7isNVH_w
https://www.youtube.com/watch?v=U97r2_O8IyI

I've just been searching around and the only issue with a power query would be that it does not allow me to loop through the signing authority hierarchy; that is to say, if the managers band does not authorise a sign-off, then my sheet needs to look for THEIR manager and confirm if they can signoff.

Thank you so much for your response, I've manually typed out the code on my other laptop and it is so much more efficient than my current code. It is still running on that machine as the data is very, very large so it might take a very long time for it to compute.

But I hit the same roadblock as I have done on my previous code, too: when the manager sign-off fails, it should loop until it goes up the hierarchy and list the next manager that can approve. I have attached the updated employee details sheet which has a new manager that should be able to approve invoice number 1.

Hi, thank you so much for the solution. My final spreadsheet looks a bit like this; is it possible to alter the formula to work on this please? The columns are different.

Hi, I really want to try this on my spreadsheet but the final spreadsheet looks a bit different to this. Can I please get the code that is relevant to the one I have pasted in please? The highlighted columns are the relevant ones.

Hi, you're right. I should've been more careful of that. I apologise for this instance and it won't happen again. It was an attempt from my side to make the task easier to explain, but it ended up not working out for me.

Hello, sorry about that. I have attached it now.