Apply VLookup equivalent to large number of rows? (30k+ rows = 2minute load time)

[alec123123]

Context:
For a specific company, a worksheet contains the drugs they used in 2022. These drugs are not listed by label name, but by an identifier code. Another worksheet contains all possible drug identifier codes and the corresponding drug label name. I need to fill a column to contain the label names for each drug identifier code that the company has listed. The number of drugs listed for a given company can be anywhere from 1k to 70k. My current version takes around 2 minutes to load when processing 30k rows (30k drug identifier codes)

My attempt:
I created a loop that goes until the cell in the drug identifier column (of destination workbook) is empty. For each cell, I use the match function to find the row # where the identifier has been matched. Since I know the column that the drug label is in, I combine that with the row to return the drug label name value.

What is fastest method to do something like this? Create array and do comparison? Dictionary? I tried filling the VLookup function down the whole column, but it was very slow doing it 1 by 1. I'm not sure why its so smooth in excel, but takes forever if I use it in a macro.

 

[offthelip]

Welcome to the forum
I would expect using the dictionary method combined with variant arrays to be the fastest. See my post on this thread which has a similar problem except they were trying to match 3 columns rather than 1 which you are.
Match function vba