100K plus lines of data, DOUBLE VLOOKUP help required.

[Noobllianz]

	A	B	C	D	E	F
1	Customer Name	Customer No#	Order #	Order Price	Order Date	Description
2	John Doe	Cust001	1000item	$1000	2011/01/01	Laptop
3	Jane Doe	Cust002	2000item	$2000	2012/01/01	Monitor
4	John Smith	Cust003	3000item	$3000	2013/01/01	Motherboard
5	Jane Smith	Cust004	4000item	$4000	2014/01/01	Keyboard
6	Doe Smith	Cust005	5000item	$5000	2015/01/01	Mouse
7	John Doe	Cust001	6000item	$6000	2016/01/01	SSD

<tbody>
</tbody>

Hi Guys.

I was able to get advice on how to partial search Customer names/customer numbers in order to get their whole history displayed.

I used the formula to refer to a SUMPRODUCT cell next to the Search cell for reference.

=IF(ROWS(G$1:G1)>$B$7,"",INDEX(Sheet1!$Q$2:$W$1000000,AGGREGATE(15,6,(ROW($Q$2:$Q$1000000)-ROW(Sheet1!$A$2)+1)/ISNUMBER(SEARCH($A$7,$Q$2:



I have heard that Double vlookups will be able to search faster.

If I wish to apply the same ROWS, AGGREGATE, SEARCH with Double Vlookup, how can i formula this so that I can search for the Customer Name, and display all relevant information more quickly without lag.

Thanks for your help!

-B

 

[TheMathLab]

Hey B.

Could you not just format the data as a table and then filter?

 

[Noobllianz]

Hey B.

Could you not just format the data as a table and then filter?

Hi!
Thank you so much for the response.

This file is in a shared folder and is being updated constantly by a separate department.

We are unable to edit or amend the master workbook.


I I was thinking a initial partial search for a single column of info(client name) then adjacent rows can be pulled using an exact match. Just want to know if there is any way to incorporate the small/row function into the double vlookup formula.


I just cant work it out.

Thanks!

 

[offthelip]

What about loading the whole table into a variant array and doing the whole thing in VBA, it will be very fast.

 

[Noobllianz]

What about loading the whole table into a variant array and doing the whole thing in VBA, it will be very fast.

So basically, I can use my external workbook and use vba from within to access data from a different work book?

Does the variant array store the whole thing in vba?

I'm a bit confused.

Can you advise on what I need to study

Thanks

 

[offthelip]

Try reading this article which should get you started:
https://excelmacromastery.com/excel-vba-array/

Try googling "variant arrays Excel VBA" and see what else you can find

The answer to your questions is : Yes and Yes in possibly a number of variant arrays