Forum Discussion
Formula speed / Laptop specification
The method that approximate matches use are much more efficient due to the data being sorted. The exact match option is linear, the function starts at the top and looks at every value until it finds what it's looking for. But, the approximate match works by repeatedly 'guessing' by selecting the value in the middle of your table and testing it for a match. If it is greater than what you're trying to match, then it throws away the bottom half of the table. If less than, then it throws away the top half (made possible because the data is sorted).
So, in this fashion, it can throw away half the table with each guess and is exponentially faster (it would only require 20 guesses to perform a lookup on a table that took up all 1 million rows of a worksheet - and the table size could double, but it would only add 1 more guess the function would have to perform).
Thank you JMB17. I will test this out thoroughly and also check out power query and will feedback here.
Have a smashing weekend
Allison
- I used to compare it to how you would look numbers up in a phone book (you wouldn't start at the beginning and check every name, but flip to the middle and see where you land). But, that analogy may be lost on the newer generations as it may be before their time, lol.
Definitely follow through on looking at power query and have a great weekend!