One of the most common questions is how can I do a VLOOKUP in Excel and sum all of the matches? You can't do it with VLOOKUP because VLOOKUP only finds the first match. It won't work with XLOOKUP either, because you can only find the last match. Instead, you can use SUMIF or SUMIFS to get the total of all VLOOKUP matches.
Transcript of the video:
How to SUM all matches using VLOOKUP. Yeah, I get this.
I understand why you want to do this with VLOOKUP. But VLOOKUP can only return the first match.
XLOOKUP has a trick out here with a -1 where it can return the last match.
But we can only get to the first and last match with VLOOKUP, x lookup, index and match.
What we have to do here is SUMIFs.
Here's the numbers we want to add up, comma, here's where we're going to look, comma, and what are we going to look for?
We're going to look for all three. Cherry, apple, banana.
Close paren. Press enter.
There's the total values for cherry.
If I select all those and look down in the status bar, 3,513.
So I know you searched for using lookup and sum together, but I think SUMIFS is the way to go.
If you like this video, please visit TL.Page/MrExcel, scroll down, you'll find my group lessons and plenty more videos. Thanks for watching.
I understand why you want to do this with VLOOKUP. But VLOOKUP can only return the first match.
XLOOKUP has a trick out here with a -1 where it can return the last match.
But we can only get to the first and last match with VLOOKUP, x lookup, index and match.
What we have to do here is SUMIFs.
Here's the numbers we want to add up, comma, here's where we're going to look, comma, and what are we going to look for?
We're going to look for all three. Cherry, apple, banana.
Close paren. Press enter.
There's the total values for cherry.
If I select all those and look down in the status bar, 3,513.
So I know you searched for using lookup and sum together, but I think SUMIFS is the way to go.
If you like this video, please visit TL.Page/MrExcel, scroll down, you'll find my group lessons and plenty more videos. Thanks for watching.
