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 Newbieexcelexcel-formula
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:
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.