Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Vlookup Based on two criteria to pull third criteria

0

I am trying to match the (Sum of amount) on the Main tab in Column C that corresponds to each policy in column A on the secondary tab that matches the account columns on the secondary tab (2412100, 2416300, and 2203400)

Only way Ive been able to do it is by pulling the accounts out into separate tabs and just doing a Vlookup say its on a tab 2100 and the policy is in A and the money is in B then Vlookup(trim(a2),2412100!A:C,3,0) - I just used one example - but thats the only way I can do it. 

But there has to be an easier way?

Answer
Discuss

Discussion

Thanks for the quick reply! I entered the =SUMIFS(Sums,Policies,$A2,Accounts,B$1) formula and got the #Name Error just like you said. Just trying to piece together either of the two suggestions you said to make the formula return the money in the (Sum of amount) column on the main tab that corresponds to that account. I was not sure where I was suppose to place the range addresses in the formula or somewhere else? Sorry! I'm trying to understand.

Also, I do not want the amount in C to sum - I need it to just return a match as it corresponds to that account as stated. I'm using Vlookup as I'm using two worksheets to pull that info into another worksheet. 
Sroncey21 (rep: 66) Jun 28, '18 at 8:49 pm
Just a little fyi, when you respond to an Answer, click the Discuss or Add to Discussion button underneath that specific answer; that way the person who wrote it will get a notification about it without having to always check this page. :)
don (rep: 1989) Jun 29, '18 at 2:55 am
Add to Discussion

Answers

0
Selected Answer

Please try this formula in cell Secondary!B2. Copy it to the right and down as far as required.

=SUMIFS(Sums,Policies,$A2,Accounts,B$1)

Sums, Policies and Accounts are named ranges. Their respective addresses are

Main!$A$2:$A$10000
Main!$B$2:$B$10000
Main!$C$2:$C$10000

While these ranges don't exist in your workbook my above formula will returns a #NAME error. If you don't want to set them up, replaces the names with the range addresses in the formula.

I used names above because they make the formula easier to read. In plain language it says, "Sum up the amounts in Sums where Policies = A2 and Accounts = B1". This logic implies that the result will show the sum of duplicates where VLOOKUP would only show the first match encountered.

Discuss

Discussion

Let's do this methodically. First, please don't enter your comment as a discussion of the question while your intention is to discuss the answer.
Second, I have attached a workbook to my answer now which shows an implementation of my formula. (You can copy the formulas further down than I did.) Please check the results and let me know if it is what you want or, alternatively, show a few lines of the result that you do want.
Variatus (rep: 4889) Jun 28, '18 at 10:36 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login