Excel Challenge: Sum all VLOOKUP Formulas - Episode 2184

Microsoft Excel Tutorial: How can you sum all VLOOKUPs in Excel

Welcome back to another episode of the MrExcel netcast, where we dive into all things Excel. In today's episode, we're tackling a question from Ron about using the old LOOKUP function in Excel. This function has been around since the days of Visical and is often overlooked, but it has a special trick up its sleeve that can come in handy when working with VLOOKUP formulas.

In this episode, we'll be using a sample table of products and their corresponding values to demonstrate how to use the LOOKUP function. Typically, we would use a VLOOKUP formula to retrieve the values from the table and then use the SUM function to get a total. However, if our goal is just to get the total value without all the individual values, the LOOKUP function can save us some time.

Unlike VLOOKUP, LOOKUP does not require us to specify the column number or whether to use TRUE or FALSE. It automatically uses TRUE and goes to the last column in the table. This makes it perfect for situations where the table is sorted and all the values we are looking up are present. We can simply enter the LOOKUP formula and press Ctrl+Shift+Enter to get the total value.

But that's not all, LOOKUP can also be used for horizontal tables by automatically switching to the HLOOKUP version. This can come in handy when working with tables that have more rows than columns. However, it's important to note that if the table does not have enough rows, the formula will return the name of the product instead of the value. So be sure to double check your table before using LOOKUP.

If you want to learn more about Excel and its various functions, be sure to check out my book, Excel 2016 In Depth. And don't forget to hit that "I" in the top right-hand corner to learn more about my latest book, Power Excel with MrExcel, 2017 edition. Thanks for tuning in and don't forget to hit that subscribe button for more Excel tips and tricks. See you next time on the MrExcel netcast!"

#excel
#microsoft
#microsoftexcel
#exceltutorial
#excelhistory
#excelformula
#excelformulasandfunctions
#excelvlookup
#evergreen

This video answers these common search terms:
how to sum a xlookup in excel
how to sum in excel using vlookup
how to sum in excell using vlookup
how to use sum formula with vlookup in excel
how to lookup and sum in excel
how to vlookup and sum in excel
how to do a vlookup sum in excel
how to use lookup and sum in excel
how to use vlookup and sum in excel
how to use vlookup and sum in excel
how to use vlookup with sum function in excel
how to use vlookup with sum in excel?

Table of Contents:
(00:00) Sum All Lookups
(00:21) Using VLOOKUP to sum values from a lookup table
(01:07) Introducing the LOOKUP function
(01:17) LOOKUP vs VLOOKUP: differences and similarities
(02:08) Using LOOKUP to sum all lookup values at once
(03:33) Explanation of LOOKUP and its special trick
(04:03) Outtake: LOOKUP can also work for horizontal tables
(04:15) Clicking Like really helps the algorithm

Many people are familiar with VLOOKUP(B4,Table,2,True)
If you are doing the True version of VLOOKUP, you can also do LOOKUP
Lookup is odd because it returns the last column in the table.
You don't specify a column number.
You don't specify True/False as the fourth argument like VLOOKUP.
Lookup has a special trick: You can lookup all of the values at once and it will sum them.
Remember to press Ctrl+Shift+Enter after typing =SUM(LOOKUP(B4:B17,E4:E8))
LOOKUP can also do the equivalent of HLOOKUP if it senses the table is wider than tall

[pirsquare]

I believe you can avoid Ctrl+Shift+Enter by using a sumproduct: =SUMPRODUCT(SUMIF(E4:E8, B4:B17, F4:F8)) :D

[MySpreadsheetLab]

Great video! I didn't know the 'Outtake' part about the lookup! Thanks Bill !

[MichaelDaniels]

You did a pod cast some time ago that showed all the ways you can select a range or next to a range. Can you lead me to that video or maybe update? Thank you. Love the videos

[aaronwinderbaum]

So what if you only wanted to find the sum of only the As??

[demonshewolf]

I have a bit of a random question involving arrays since I can't seem to find the answer anywhere. I would like to put recurring appointments in a day planner format (vertex42) by date and time from a list I create in another sheet in the workbook. Is that possible?

[Entrepreneur-jxll]

I am attempting to write a formula that will determine the number of times a certain number has come up (frequency) and the average days between each appearance. For example: #24 has been drawn 36 times in a year and the average lapse in time before it appears again is 24 draws. There are, of course, multiple numbers that require this calculation. Also, after the completion of this, I want the numbers that are coming due to hit to complete an "ALERT" somehow. Is this possible?

[reyvinai]

Incase If I would like to get the value as per the name like "A" repeated thrice it has to give the output as 90, how to imply the formula for this scenario?

[MichaelDaniels]

Do you have an easy way to get a column of data (ie: C19:C200 ) and I want to bring the data in F3:Q15 . When I try to copy or drag a formula it does not do right. Can you help with a formula to do this? Thanks

[NishantGhosh]

Just another way without CSE: =SUM(VLOOKUP(T(IF({1}, B4:B17)), E4:F8, 2, ))
Or
=SUM(LOOKUP(T(IF({1}, B4:B17)), E4:F8))

[HamidurRahman]

I all time appreciate your video guideline, I always see your tutorial, , very good & easy presentation . I also trying to deliver, you can see and any suggestion me pls.

[miqbal]

Using lookup show sum of same
individual repeated values in column in excel

[Reduce_Scan]

Hi Bill
I have two questions
1-
Do a version Office 365 Enterprise E5
with additional things for a version 365 pro plus in excel or no
2-
Is microsoft see len&lenb
two functions
or function have two versions

[blackburd]

You were going good and then just said "press ctrl shift enter" and stopped teaching. WTH is going on with this? also, does not work for me