VLOOKUP, 2 lookup values; then sum the lookup returned

Group, thank you in advance for the assistance.
Please see attached file.

I want to conduct a vlookup based on two lookup values. The table I am performing the lookup on may have multiple rows that match the criteria. I want the Sales Volume sum of all applicable rows.
I see many formulas that reference all cells in a table, let's say $G$2:$J$25. How can I write this formula where data will be added to the table via a source data refresh without having to adjust the formula. That is one reason I liked the vlookup, because it will search the entire column.

I am attempting to build the formula in C2:C11, lookup column A and B info in the table starting in G.

Thank you,
Price
Multile vlookup values Then Sum-Example.xlsx

Hello Price,

Since you are eon Excel 2007, you can use SUMIFS

=SUMIFS(J:J,G:G,A2,H:H,B2)

Not VLOOKUP, SUMIFS:

Formula:

Please Login or Register  to view this content.

Regards, TMS

I should point out that the Table does not need to refer to the whole columns, just the rows where you have data.


Regards, TMS

Thanks to the both of you for the responses, I can't seem to get the formula to work.
Do you mind utilizing it in the spreadsheet, or writing it out with an explanation for each portion of the formula.

@TMS: I was wanting it to refer to the entire column instead of just the rows of data so that I won't have to adjust the formula as more data is added.

As Mr TMS mentioned u don't need to adjust the ranges if you ref a table, but for normal ranges u could use

Formula:

Please Login or Register  to view this content.

I was wanting it to refer to the entire column instead of just the rows of data so that I won't have to adjust the formula as more data is added.

It's a Structured Table. If you use Structured Table References, as I have in the formula provided, you don't need to worry about it ... Excel will maintain the table and the associated Named Range. Just entering a value in the row below the table will include the row in the Table.

Formula:

Please Login or Register  to view this content.

Making the Table include whole columns is likely to make the whole workbook slower calculating.


If you don't want to use the functionality built into structured tables, you could create a Dynamic Named Range, or simply refer to the columns in the table.


See the attached example

Regards, TMS

When I read TMS's "I should point out that the Table does not need to refer to the whole columns, just the rows where you have data." I must have misinterpreted this statement. I am familiar with table references, I use them regularly and my mind must have been elsewhere when I read this.

Oh, and I found that I was attempting to utilize SUMIF instead of SUMIFS. That is more than likely when my mind wondered. Oops..

In other news, do you know how I could then incorporate (for an additional column) a moving average of last three months.
I found =AVERAGE(OFFSET(ref,COUNT(ref:ref)-3,0,3,1)) but struggling to incorporate into our formula.

I want to use the SUMIFs then complete calculation to return last 3 months moving average. I added some months to the data and reattached, is it a problem that it is formatted as text?

Revised_Multile vlookup values Then Sum-Example TMS.xlsx

In other news, do you know how I could then incorporate (for an additional column) a moving average of last three months.

Strictly speaking, your original question has been answered and this constitutes scope creep.

You would do better to start a new thread for the new question to generate new interest.

Regards, TMS



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.