Spreadsheet that may have criteria that is duplicated

[ps3623]

I need some help. I have a report that is downloaded into an excel format which was small for one month but as I am getting information from other months it has now grown. I have to find certain criteria and report the total for that criteria. Need to find 3 matches and get the total of the matches Here is my problem. Need to match Column A first which is always a different number, then match column B which may have the same number down that column within the entire report. Then I need to match the word Total which is on Column C which is the same within the entire report and get the result on Column E across from Total. Sometimes the word Total may be one row down from the first two criteria's or maybe 2 rows down or even 3 rows down. This report is going to continue to grow so the criteria's will not be in the same row all the time. The Columns will be the same. I had previously gotten help which worked with the small report but not with the larger report. Below is the formula that worked with the small report. This report has other information on other columns but I only need to match column A-E. I created an example which is attached

=VLOOKUP("total",INDEX($C$1:$C$99,AGGREGATE(15,6,ROW($A$1:$A$99)/($A$1:$A$99&$B$1:$B$99="5520"),1)):$E$99,2,)

[Bo_Ry]

The current formula ($A$1:$A$99&$B$1:$B$99="5520") is for look at 55 from column A and 20 from column B
At the end 2,) 2 is for column index that vlookup will return value, if you want to return number in colums E then from C to E is 3 column

=VLOOKUP("total",INDEX($C$1:$C$99,AGGREGATE(15,6,ROW($A$1:$A$99)/($A$1:$A$99&$B$1:$B$99="5520"),1)):$E$99,3,)

[ps3623]

Using this formula I go the result for 5520 which was 50 but if I want to get the result for 55 40 I get a #NUM!, which I should get 200. I have to get various totals from this spreadsheet. Column A is the main category and Column B is the subcategories for Column A. So the 40 could also be under other main categories.

[Glenn Kennedy]

One way:

=VLOOKUP("total",INDEX(INDEX($C$1:$C$100,MATCH($H$2,$A$1:$A$100,0)):$C$100,MATCH($H$3,INDEX($B$1:$B$100,MATCH($H$2,$A$1:$A$100,0)):$B$100,0)):$E$100,3,FALSE)

I picked up col A and col Bvalues from H2 and H3, to make life easier...


see sheet.

[Bo_Ry]

Please try
=VLOOKUP("total",INDEX(C:C,MATCH(40,INDEX(B:B,MATCH(55,A1:A99,)):B99,)+MATCH(55,A1:A99,)):E99,3,)

[ps3623]

Thank you both I used Glen's way and it worked.

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.