Authentic voices. Remarkable stories. AOL On Originals showcase the passions that make the world a more interesting place.
Hank Azaria’s touching, humorous, and often enlightening journey from a man who is not even sure he wants to have kids, to a father going through the joys, trials and tribulations of being a dad.
Gwyneth Paltrow and Tracy Anderson spend time with women who've overcome hardship, injury, and setbacks to triumph in the face of adversity.
Enter the graceful but competitive world of ballet through the eyes of executive producer, Sarah Jessica Parker. This behind-the-scenes docudrama reveals what it takes to perform on the ultimate stage, the New York City Ballet. Catch NYCB on stage at Lincoln Center.
ACTING DISRUPTIVE takes viewers inside the businesses and passion projects of Hollywood’s top celebrities.
Explore what it means to be human as we rush head first into the future through the eyes, creativity, and mind of Tiffany Shlain, acclaimed filmmaker and speaker, founder of The Webby Awards, mother, constant pusher of boundaries and one of Newsweek’s “women shaping the 21st Century.”
They say every picture tells a story and AOL On's new original series My Ink proves it. Travel along as some of the world's greatest athletes bring their tattoos to life through exclusive interviews and visits to their favorite tattoo parlors.
Discover crowdfunded small business success stories with author, comedian, and entrepreneur Baratunde Thurston.
Go behind-the-scenes with racing's hottest, young talent, 17-year-old Dylan Kwasniewski, as he aspires to make it in the #1 motorsport in America – NASCAR
Follow Scott Schuman, the Sartorialist, from the streets of NYC to the capitals of Europe on his quest to photograph and document the best in culture and fashion.
Iconic potter, designer, author and personality Jonathan Adler shares his unique perspective on creativity. Showcasing the inspiration Jonathan finds in the most unlikely people and places, Inspiration Point will add style, craft and joy to your life.
Serving Innovation gives a fresh look into the stories and passions that motivate some of the most innovative tastemakers in America.
A documentary directed by Alex Winter exploring the Napster downloading revolution; the kids who created it, the bands and businesses that were affected and its impact on the world at large.
Nicole Richie brings her unfiltered sense of humor and unique perspective to life in a new series based on her irreverent twitter feed. The show follows the outspoken celebrity as she shares her perspective on style, parenting, relationships and her journey to adulthood.
Today's dueling Excel podcast question is how to return the sum of all columns from a VLOOKUP. Mike and Bill show you various ...
methods in episode 1161.
Tags:How to Return all Columns Sum using Excel VLookup ,Excel Lesson,Excel Podcast,excel VLOOKUP tutorial,Excel VLookup Function,mr excel,VLOOKUP All Columns,VLOOKUP Worksheet Function
Grab video code:
Bill Jelen: Hey! I’m Bill Jelen from Mr. Excel.com and I’ve got a cool Excel tip for you today.
Mike Gel Girvin: Hey! This Mike Gel Girvin on Excel is Fun in YouTube and I have a different way to do that.
Bill Jelen: Hey, welcome back. It’s Friday, that means it’s time for another dueling Excel podcast. I’m Bill Jelen from Mr. Excel and Mike Girvin from Excel is Fun will be joining us. Mike sent me this question, he says “All right someone wants to do a look up. Product four, when they find product four they want to multiply all those numbers, eight times nine times five times eight times four times the quantity.” All right now, actually this will be a great duel. I can only think of one way to do it. Sum product, =SUMPRODUCT so sum product is going to multiply a whole bunch of stuff together, the stuff in this case is going to use the offset function. So offset, offset is very powerful. We are going to start from this top left corner cell right there, press the F4 key. It says how many rows down and we want to do. Well, okay to get the number of rows down, I’m going to use the match, go find this value over here, the product four within this range of products. I’ll press F4 there and comma zero to force an exact match. That will tell me how many rows to go down.
Next how many columns to go over, well that’s zero, how many rows total do I want, that’s one. How many columns wider I want, that’s five. All right, so we have to close the offset, close the sum product, press Enter. That’s the 34 good and then times the number to the left of me. I’m in something called edit mode here I want to be in point mode so I’m going to press the F2 key and now I can just hit the left arrow, there we go and the answer should be the this higher 170. We’ll try it down here, yup, those comments are driving me insane, let’s undo, undo. We will copy and then paste special formulas, there we go. Looks like it’s working and then over here, paste special formulas, and here paste special formulas. There we go. All right Mike, let’s see what you got.
Mike Gel Girvin: Thanks Mr. Excel. Hey, this is a great problem, this actually was sent in from Abstractus from YouTube. He is a cake manufacturer and I actually already did a video on this topic. But there’s so many cool ways to do this and of course I suggest this to Mr. Excel for a duel. All right, so way I’m going to do this here is our products, the quantity and we need a total difficulty scores of five times the whole row for product four in this lookup table.
Now, I’m going highlight all of the cells that should get this formula right up front and I’ve highlighted that column. Now, I’m going to hold control and then highlight this one. I’m aware of where the active cell is because when I do more than one cell formula at one time, you got to build the formula from the point of view of the active cells. So here we go, I’m just going to use Vlookup right? Vlook up, we’ll jus look up this product, right. So, look up this product that will be a relative cell reference. By the way, I’ve highlighted them all so as soon as I finished the formula I just Ctrl+Enter and it populates all of those cells.
The table, the array, I’ll just get this whole table right here, the lookup column is going to be the first column of course that’s the way Vlookup works. I’m going to hit the F4 key and then comma, oops, column index? But wait a second, two, three, four, five and a six, we need all five columns two to six. So, I’m just going to put curly bracket, two comma three, comma four, comma five, comma six, and curly bracket. Wow, this is an array built right into the formula and as soon as we do that, this becomes in a ray formula because it’s going to return all of the five numbers so it’s returning more that one number so it’s a ray. Comma and this is an exact match. So, I’m going to do zero for exact match, close parenthesis.
Now, to see that this works, I’m going to actually highlight this and hit the F9 key that evaluates it, 8, 9, 5, 8, 4. So, it looks like it’s working. I’m going to Ctrl + Z. Now, that is an array of values and we want to put this in some product just like Mr. Excel did, array one and then I’m going to comma and array two. Well, I just want this. But sum product will not allow arrays of different dimensions. This first lookup has given us a one by five and this cell is a one by one. We can get around that comma problem requiring the same dimension by using multiplication. So, there we go, close parenthesis. Now, I’m going to Ctrl+Enter to populate all the cells, oops, I want some blanks down here, until I fill this in with the active cells still selected. I’m going to hit the F2 key edit and I’ll just put a little if. If two cells to my left equals blank, double quote, double quote, then what do I want, the screen tips helps me out here, double quote, double quote comma and then the value of false is that big thing right there. So, I come to the end, close parenthesis. I hold Ctrl and tap Enter to repopulate all the cells. So now, if I select product two and we made 43 of them that should work just fine.
All right, we’ll see your next trick.
Bill Jelen: Hey thanks Mike, that is an excellent trick using two three four five and six as an array for the return column. What a cool trick definitely a point to Mike. Hey! I want to thank every one for stopping by. We’ll see you next week for another dueling podcast from Excel is Fun and Mr. Excel.