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

This looks more like a job for a pivot table. Create a pivot table and add User Name and Report Name (in that order) to the Rows field, and add Report Category to the Columns and the Values fields of the Pivot table and you will be close to what you want. — user844705
Definitely looks like a pivot table job. You would have to dynamically figure out how many times to use each formula which would end up being something for VBA, but there isn't a point to doing that when a pivot table will do it faster. — KFichter
@ojf I have a pivot table to do that but my problem is that 1) the pivot table doesn't structure it correctly for its intended use and 2) it requires work for the user who might not understand pivot tables very well to know how to get what they need. The only way I can make it so that all they have to do is input their info in one sheet is through the use of formulas — THAT newbie
I guess it would have been better to say that I need a function version of a pivot table but COUNTIFS came up in my mind first — THAT newbie
There is a formula and I can write it for you, but it will have to be later tonight when I get off work. If that works for you. — rwilson

rwilson rwilson · Accepted Answer · 2015-08-06T00:38:37

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:

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.
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.

COUNTIFS for VLOOKUP

1 Answers