"Double VLOOKUP" Range criteria

[Rob_66]

I found a fast way of utilising VLOOKUP when having huge amount of data (I'm looking at 1,000,000 rows of data looking up another table of 200,000 to 500,000 rows (& 15 columns, if that as any bearing).

I used the following:
=IF(VLOOKUP(id,data,1,TRUE)=id, VLOOKUP(id,data,col,TRUE), NA())

This was HEAPS faster, but when I used a data array defined like $A:$M the searched missed a lot of matches. This was solved by puting in the exact range eg $A5:$M221878

I would like to have a generic catch-all range so I don't have to keep editing this that wont miss any matches. Any suggestions?

 

[RasGhul]

Index & match is faster than vlookup as vlookup has to lookup through the whole array.

If you want to make your formulas expand dynamically you could convert your data to tables and use table referencing in your formulas or you could use dynamic named ranges.