Problem: Are there any other arcane tricks with the old LOOKUP function that you can use to close out this string of topics on lookup?
Strategy: I am glad that you asked!
Say that you want to figure out the total bonus payments for the month so that you can accrue money to pay the bonus. You aren't ready to pay the bonus yet, so you don't have to do all the lookups. You just want one formula that does all of the lookups and totals the values.
Using SUM(VLOOKUP()) will not work, even if you use Ctrl+Shift+Enter to make it an array formula.
However, using SUM(LOOKUP()) with Ctrl+Shift+Enter will correctly do all the individual lookups and sum them.
Gotcha: As mentioned in the last topic, the LOOKUP command only does the approximate-match type of lookup, so this trick is likely only useful to the SCAIA (aka Scientists, Commission Accountants, and IRS Agents for those of you who have not been paying careful attention.)
Type the formula =SUM(LOOKUP(C2:C26,E2:E6,F2:F6)) but do not press Enter.
- One formula does many lookups.
Instead, hold down Ctrl+Shift. While holding Ctrl and Shift, then press Enter. In the formula bar, Excel will add curly braces around the formula. The result is correct.
- LOOKUP can return an array. VLOOKUP can not.