Vlookup nightmare in large data set

[zakynthos]

Hi,

Columns E,F, G on a sheet called 'Briefings' contain the following formula:

=VLOOKUP($B4,'Peoplesoft Data'!$A:$C,3,FALSE)
=VLOOKUP($B4,'Peoplesoft Data'!$A:$D,4,FALSE)
=VLOOKUP($B4,'Peoplesoft Data'!$A:$E,5,FALSE)

Column K on this sheet has a conditional formula:

=IF(AND(B4='Merlin data'!C2,Briefings!I4='Merlin data'!M2,'Merlin data'!B2>0),1,0)

My problem is extremely slow/static running, I assume because of the extremely large data set being referenced on the sheets 'Peoplesoft Data', Merlin data etc


Data on Briefings is typically about 10,000 rows
Data on People Soft is typically about 20,000 rows
Data on Merln data sheet is 100,000 rows!!!

Ideally I would like to convert the 3 Vlookups and the conditional function above to VBA and if so, would this speed up the problem. Could they reference the data sets without the need to import them into the file first? The 3 files are:

Briefing ID Tracking.xlsx
Peoplesoft_Data_CS_and_Cons_Sales.csv
Merlin Data.csv


Any help would be most welcome - the file is now over 30 Mb after importing the data files and any amendment, save etc takes about 10 min to update or freezes - a nightmare!

 

[zakynthos]

Thanks, yes, I could do that

 

[Aladin Akyurek]

Thanks, yes, I could do that

In that case, we can implement a fast set up for retrievals...

=IF(LOOKUP($B4,'Peoplesoft Data'!A:A)=$B4,LOOKUP($B4,'Peoplesoft Data'!A:A,'Peoplesoft Data'!C:C),"Not Found")

Apply the same logic for other retrievals...

 

[zakynthos]

Many thanks for this help! - I'll give it a try and compare timings to implement with the VBA code example given above which I've managed to adapt and run successfully. I'll use whichever runs faster.

 

[Aladin Akyurek]

Many thanks for this help!

You are welcome.

- I'll give it a try and compare timings to implement with the VBA code example given above which I've managed to adapt and run successfully. I'll use whichever runs faster.

Please don't forget to report back.