The question: "Why won't VLOOKUP add up all the matching results?" Today, Episode #1396 will show us how to use the SUMIF function instead of VLOOKUP. Included in today's episode... "Why it is important to lock the rows in your VLOOKUP table range."
Transcript of the video:
Learn Excel from MrExcel podcast. Episode 1396 VLOOKUP Total.
Hey all right. We've got a question sent in.
Someone who's trying to do some VLOOKUPs, and they were surprised that this part number is returning 2 instead of the total of those.
and said you know how can we how can I make the VLOOKUP give me the total of all of them?
Well what we're going to do is we're going to switch from VLOOKUP to a SUMIF SUMIF we're going to look through all of the items over in column A and see if it's equal to this particular part number.
If it is we want to add up the corresponding number from column B.
Now it's very important that as you're building this formula, and here actually I'm going to do it for you.
We came over here to A2 I'm going to do control+shift+down arrow to select that whole range and then press the F4 key the F4 key puts dollar signs before the A and the 2 and A and the 26 or see if it's equal to this part number.
We're here to the left of this and then the sum range has to be the exact same shape as the original range and again we press F4 there. Those dollar signs are really important as we copy the formula down because I want to make sure that's always pointing to the same range and I can see here that there were some #N/As that were showing up and I went to look and when a person created the lookup in the first place they said they failed to put the dollar signs there in the lookup table and the problem with that is that as you copy that VLOOKUP down, you know here it starts in A2 while here it goes to A3 and then A4 and in A5 and you're still getting results because you got most of the table But then as you get about halfway down.
You know you're losing all the items from row 2 through row 9.
That's why we have to use those absolute references so right back up here, if we were going to go with VLOOKUP I would at least put a dollar sign before the 2 and a dollar sign before the row number.
Copy that and then Edit Paste Special Formulas to copy those formulas down. You'll see that we're getting results the whole way out.
Those results still don't add everything up. You need to go out to SUMIF but just for the casual VLOOKUP person, it's really important that that second range of the LOOKUP table has at least the row numbers.
There you have it, I want to thank you for stopping by. Will see you next time for another netcast from MrExcel.
Hey all right. We've got a question sent in.
Someone who's trying to do some VLOOKUPs, and they were surprised that this part number is returning 2 instead of the total of those.
and said you know how can we how can I make the VLOOKUP give me the total of all of them?
Well what we're going to do is we're going to switch from VLOOKUP to a SUMIF SUMIF we're going to look through all of the items over in column A and see if it's equal to this particular part number.
If it is we want to add up the corresponding number from column B.
Now it's very important that as you're building this formula, and here actually I'm going to do it for you.
We came over here to A2 I'm going to do control+shift+down arrow to select that whole range and then press the F4 key the F4 key puts dollar signs before the A and the 2 and A and the 26 or see if it's equal to this part number.
We're here to the left of this and then the sum range has to be the exact same shape as the original range and again we press F4 there. Those dollar signs are really important as we copy the formula down because I want to make sure that's always pointing to the same range and I can see here that there were some #N/As that were showing up and I went to look and when a person created the lookup in the first place they said they failed to put the dollar signs there in the lookup table and the problem with that is that as you copy that VLOOKUP down, you know here it starts in A2 while here it goes to A3 and then A4 and in A5 and you're still getting results because you got most of the table But then as you get about halfway down.
You know you're losing all the items from row 2 through row 9.
That's why we have to use those absolute references so right back up here, if we were going to go with VLOOKUP I would at least put a dollar sign before the 2 and a dollar sign before the row number.
Copy that and then Edit Paste Special Formulas to copy those formulas down. You'll see that we're getting results the whole way out.
Those results still don't add everything up. You need to go out to SUMIF but just for the casual VLOOKUP person, it's really important that that second range of the LOOKUP table has at least the row numbers.
There you have it, I want to thank you for stopping by. Will see you next time for another netcast from MrExcel.
