Return Multiple values matching the criterion in column

[amit_gupta123]

Hi All

I am working with 2 million rows and come across a macro for fast lookup which states about Scripting Dictionary object. As i tried to use it, it worked tremendously fast but the issue is when it matches the value in Col A from sheet 1 and searches in master data in Sheet 2, it gives single match and that too from bottom of the list. The result I get is in Sheet 1 with headers highlighted as Blue, however the desired one is highlighted as yellow. I am not at all able to tweak and hence looking forward. File is attached herewith for perusal...Regards

 

[Ateeb Ali]

check attached if it helps
press like button if problem solved

 

[amit_gupta123]

Thanks Ateeb for revert

Challenge is I m dealing with millions of rows and hence formulas making it way slower

 

[Ateeb Ali]

Dear then use countif and vlookup or indexmatch function, it will work fine

 

[vletm]

amit_gupta123
Your sample data is ... short
but
Could You do it 'opposite' way?
Here two versions
a) 'ALL' shows ALL from Sheet2
b) 'Named' show those from Sheet1

 

[amit_gupta123]

Dear Vletm...appreciate your response

Please note the following :

1. Looking for results for search list mentioned in sheet 1 out of master data in sheet 2. Sheet 1 might contain 1 lakh records and master is comprising of 20 lakh rows data

2. In the file attached by me, macro is working very fast and giving output quickly but the only challenge is for record having multiple matches are not appearing horizontally

3. It would be great if you have a look t code mentioned in my file and do some magic

 

[Marc L]

Hi.​

​

2. So I suspect a bad Dictionary use which stores only the last value rather than all values, check the logic …​

​

 

[vletm]

amit_gupta123
as I wrote - Your sample data is ... short.
You seems not even tested those two versions ....
Your notes:
1) You're looking for results ... so? ... both of those would give result.
2) ... then Your macro do not give ... expected results.
3) I looked ... and that's why I tried to offer something which works.