MrExcel.com - Your One Stop for Excel Tips & Solutions

Sum All of the Lookups

May 04, 2022 - by

Sum All of the Lookups

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.

The old LOOKUP function can lookup all part numbers in a single formula, provided you send the results into a wrapper function such as SUM or MAX.
Figure 451. 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
Figure 452. LOOKUP can return an array. VLOOKUP can not.

This article is an excerpt from Power Excel With MrExcel

Title photo by Markus Spiske on Unsplash