Vlookup spill error on huge data data

[earthworm]

I have a huge data set around 900000+ .
When I am trying to apply vlookup as an array that is by selecting all lookup value In a single cell the result is not being spill. Even though there is no value present below. It's working and spilling result on large data like around 50,000 but on huge length the spill is just not giving result.
When I press enter upon entering the formula nothing happens but when I again re enter the formula only zero value appears. Please advice I am stuck

 

[Legacy 143009]

INDEX & MATCH may work better

 

[earthworm]

INDEX & MATCH may work better

Will this spill ?

 

[Legacy 143009]

Ok, where is your value? Which column do you want to look up, which column do you want to return?

I can make you an example

 

[earthworm]

It turns out it's working. Since data is huge it takes little while when I press enter . But result is display. Thank you all for your efforts. It works. Since data was huge it took like 20 seconds to display result.