Do All Lookups and Sum the Results
February 09, 2018 - by Bill Jelen
Ron wants to do a bunch of VLOOKUPs and sum the results. There is a single-formula solution to this problem.
How can you sum all VLOOKUPs without doing each individual lookup?
Many people are familiar with:
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
=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:
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.
Title Photo: grandcanyonstate / pixabay