MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Do All Lookups and Sum the Results


February 09, 2018 - by Bill Jelen

Do All Lookups and Sum the Results

Ron wants to do a bunch of VLOOKUPs and sum the results. There is a single-formula solution to this problem.

Ron asks:

How can you sum all VLOOKUPs without doing each individual lookup?

Many people are familiar with:

=VLOOKUP(B4,Table,2,True)

If you are doing the approximate match version of VLOOKUP (where you specify True as the fourth argument), you can also do LOOKUP.

Lookup is odd because it returns the last column in the table. You don't specify a column number. If your table runs from E4 to J8 and you want the result from column G, you would specify E4:G4 as the lookup table.

Another difference: you don't specify True/False as the fourth argument like you would do in VLOOKUP: the LOOKUP function always does the Approximate Match version of VLOOKUP.


Why bother with this ancient function? Because Lookup has a special trick: You can lookup all of the values at once and it will sum them. Instead of passing a single value such as B4 as the first argument, you can specify all of your values =LOOKUP(B4:B17,E4:F8). Since this would return 14 different values, you have to wrap the function in a wrapper function such as =SUM( LOOKUP(B4:B17,E4:F8)) or =COUNT(LOOKUP(B4:B17,E4:F8)) or =AVERAGE( LOOKUP(B4:B17,E4:F8)). Remember, you need a Wrapper function, but not a rapper function. =SNOOPDOGG(LOOKUP(B4:B17,E4:F8)) won't work.

There is a really common mistake that you will want to avoid. After typing or editing the formula, do not press Enter! Instead, do this three-finger keyboard trick. Hold down Ctrl and Shift. While holding down Ctrl and Shift, press Enter. You can now release Ctrl and Shift. We call this Ctrl + Shift + Enter, but it really has to be timed correctly: Press and hold Ctrl + Shift, Press Enter, Release Ctrl + Shift. If you did it correctly, the formula will appear in the formula bar surrounded by curly braces: {=SUM(LOOKUP(B4:B17,E4:F8))}

Side Note

LOOKUP can also do the equivalent of HLOOKUP. If your lookup table is wider than it is tall, LOOKUP will switch to HLOOKUP. In the case of a tie... a table that is 8x8 or 10x10, LOOKUP will treat the table as vertical.

Watch the video below or study this screenshot.

Sum All Lookups
Sum All Lookups

Watch Video

Title Photo: grandcanyonstate / pixabay


Bill Jelen is the author / co-author of
Excel Subtotals Straight to the Point

I used to use the Subtotals feature daily after downloading mainframe data. This book covers every tip and trick for using Subtotals.