The catch is that not all of the 1000 invoice numbers exist in the source data.
In fact,most of the invoice numbers do not appear in column B.
Although this formula uses VLOOKUP twice, it runs quickly.
With very large sets of data, the calculation time can beminutes.
This is sometimes referred to as alinear search.
Approximate-match VLOOKUP is very fast
In approximate-match mode, VLOOKUP is extremely fast.
For a complete VLOOKUP overview with many examples, seeHow to use VLOOKUP.
However, the problem is that VLOOKUP won’t return an error if a value is not found.
Instead, it may return an incorrect result that looks completely normal.
So, while the formula runs very quickly, the result is not reliable.
Obviously, this is not something you want to explain to your boss.
This may seem counterintuitive because we are calling VLOOKUP twice.
However, two fast operations are better than one slow operation.
The trick is to structure the formula in a way thatrequires an exact match result.
If wedo find the lookup value, we spin up the second lookup.
Otherwise, we return an error.
Remember,VLOOKUP defaults to an approximate match.
Then we test the result against the lookup value.
In this configuration, VLOOKUPruns very fast.However, VLOOKUP may return an incorrect result.
One way around this problem is to use VLOOKUP twice, both times in approximate match mode.
#N/A means “not available” or “no value available”.
you’re able to use the NA function to display the #N/A error when information is missing.
