I never ever use vlookup for different reasons: it's a heavy computation function and I always resolve to find the data I'm looking for with different and much lighter solutions. (Which solution depends on the dataset of course)
How does OP solution compare with vlookup on computation? This is far from a trivial problem if you are operating on a 200k rows dataset (and for some reasons you have to use excel)
At first glance it looks similarly hellish, but I would be glad to be wrong.
The column order thing is pretty annoying but IMO the worst thing about vlookup is that you have to remember to set the last parameter to FALSE for pretty much every single vlookup you ever do.
(the default of TRUE is some backwards-to-the-80s-compatible behaviour where it expects the lookup column to be sorted and will return the wrong answers if it isn't, plus will return matches even if the data you're looking up isn't there - the closest match if your data is sorted, but it probably isn't, and "closest" may not even mean anything...)
4 comments
[ 3.9 ms ] story [ 24.0 ms ] threadHow does OP solution compare with vlookup on computation? This is far from a trivial problem if you are operating on a 200k rows dataset (and for some reasons you have to use excel)
At first glance it looks similarly hellish, but I would be glad to be wrong.
(the default of TRUE is some backwards-to-the-80s-compatible behaviour where it expects the lookup column to be sorted and will return the wrong answers if it isn't, plus will return matches even if the data you're looking up isn't there - the closest match if your data is sorted, but it probably isn't, and "closest" may not even mean anything...)