Microsoft Office Tutorials and References
In Depth Information
To make Juan Pablo Gonzalez’s function easier, Al_B_Cnu wrote Fuzzy-
VLOOKUP and FuzzyHLookup . Like VLOOKUP, the FuzzyVLOOKUP function
can return a specifi c column from a table. It can also return the best match,
second-best match, and so on. In Figure 129, FuzzyVLOOKUP returns the
three best matches for each forecasted customer. Someone is going to have
to go through the choices to fi gure out which entries are correct matches.
Figure 129. FuzzyVLOOKUP compares every item in the list to
fi nd the best, second-best, and third-best matches.
Summary: Custom functions in VBA help solve the fuzzy match problem.
Challenge: Two Excel gurus walk into a bar. One of them says it is faster
to use =MAX(0,MIN(A2,B2)) , and the other thinks it is better to use
=MEDIAN(0,A2,B2) . Which one is correct?
Solution: You can fi nd the answer by fi ring up a VBA macro to calculate each
formula 50,000 times. Before the macro starts, you save the value of Timer
to a variable. When the 50,000 calculations end, you can compare the original
and fi nal values of Timer .
On a Windows PC, Timer shows the number of seconds and fractional seconds
elapsed since midnight. On a Mac, the function returns only whole seconds but
no fractions. You need to make the process repeat enough times to actually
show a difference in the number of seconds. Also, you need to make sure that
the process does not extend past midnight!
Search JabSto ::

Custom Search