COUNTIFS for VLOOKUP

Question

Hopefully this headache can get resolved. I am currently trying to find a function that will do a vlookup using multiple criteria. Something similar to how a COUNTIFS function works or a function version of a Pivot Table. It may have to do with an Array Function but I can't quite figure it out. I think the best way to describe what I am trying to do is with an example:

Report Name        User Name      Report Category
Report 1           John Smith     Sales
Report 1           Jack Black     Sales
Report 1           Connie Rae     Sales
Report 1           Brain Bonds    Sales
Report 2           John Smith     Sales
Report 2           Connie Rae     Sales
Report 3           Jack Black     Inventory

The goal of the function is to be able to have it look up John Smith as one criteria and Sales as another criteria and record the reports that he used. The output would be on a different sheet and would look like:

User              Report Name    Report Category
John Smith        Report 1       Sales
                  Report 2       Sales

Connie Rae        Report 1       Sales
                  Report 2       Sales

Brian Bonds       Report 1       Sales

Jack Black        Report 1       Sales

The name I would type in and the function would be in the Reports Name column. Been playing with functions for a while but haven't had any luck. Figured I'd try here while I keep playing around.

Thanks, THAT Newbie

Answer 1

What you are asking for here is pretty complex, but if you must have a formula... Place this formula in cell B2 and copy it down. You are going to need to enter this formula with CTRL + SHIFT + ENTER:

=IFERROR(LOOKUP(SMALL(IF(LOOKUP(REPT("Z",255),$A$2:INDEX(A:A,ROWS(A$1:A2)))=Sheet1!$B$2:INDEX(Sheet1!B:B,COUNTA(Sheet1!B:B)),IF(C2=Sheet1!$C$2:INDEX(Sheet1!C:C,COUNTA(Sheet1!C:C)),ROW(Sheet1!$A$2:INDEX(Sheet1!A:A,COUNTA(Sheet1!A:A)))-ROW(Sheet1!$A$2)+1)),COUNTIF(INDIRECT(ADDRESS(LOOKUP(9.99999999999999E+307,MATCH(A$1:A2,A$1:A2,0)),COLUMN()+1)&":"&ADDRESS(ROWS(A$1:A2),COLUMN()+1)),C2)),ROW(Sheet1!$A$2:INDEX(Sheet1!A:A,COUNTA(Sheet1!A:A)))-ROW(Sheet1!$A$2)+1,Sheet1!$A$2:INDEX(Sheet1!A:A,COUNTA(Sheet1!A:A))),"")

This formula assumes the first header, on both sheets, is in cell A1 and the last (third) header, on both sheets, is in cell C1. Also, the formula references "Sheet1", so you will need to change this to the actual sheet name. You can use the images below to line up the formulas:

A couple of things to point out:

1. The formula uses dynamic ranges when referencing the data sheet (Sheet1), which means you can just continue to add data to the table as it comes in. However, you will need to restructure your table on the user sheet (The worksheet with the formula) as data is added.

2. The formula takes into account that the user may have multiple report categories (Sales, Inventory,etc.).

Let me know if this works for you and if you need me to adjust anything.