We help IT Professionals succeed at work.

Excel lookup with multiple (and missing) parameters

capture
Sorry cant send actual files.

What is the formula that will populate the yellow cells with the list of names that fit the parameters of the green cells.

In this example, Metric1 where values are in within the low/high range, and Metric2 higher than low parameter, AND will also accept blank parameters?
Comment
Watch Question

Rob HensonFinance Analyst

Commented:
I would suggest using a Pivot Table and apply filters to the metrics.

Author

Commented:
Thanks, but would need to avoid pivot tables. Hoping for a clever index/match/sumproduct kind of thing......
Rob HensonFinance Analyst

Commented:
Any particular reason for avoiding Pivot Table? This is exactly what they are designed to do, summarise data with criteria where required.

Author

Commented:
would a pivot table filter take ranges as inputs?
Rob HensonFinance Analyst

Commented:
With VBA the Pivot can be automated to take inputs from ranges if required.

Take a look at attached instead, uses the High/Low criteria to determine whether a name matches the criteria, you can then  filter on the match. This creates the list in place rather than in the separate cells. Select the dropdown in cell J1 and filter on True values.

You could use Advanced Filter instead, this can also be automated with VBA if required.
Metric-Filter.xlsx
Finance Analyst
Commented:
File updated to show Advanced Filter option.

Second sheet shows result after filtering.

To do Advanced Filter,

Firstly delete the results in cells A11 to D12, leaving the header names in row 10.
Put cursor in the list of names and metrics before selecting the Advanced Filter option on the data tab, Sort & Filter Group
The AF needs 3 input ranges:
Data List - should be selected automatically if cursor was correctly located

Criteria list: Select cells M1 to R2 - these cells have formulas based on your High/Low inputs, where the high/low is blank it uses the min/max value

Copy to range: this is greyed out until you select the Copy to another location option at the top, select that and then choose cells A10 to D10. These cells have to match the headers of the data list but can be in a different order if so required and don't need to include all headers.

Click OK, the results of the filter will be placed below the header cells.

For reference, the criteria cells are treated as AND conditions when on the same row, putting them on subsequent rows would make them OR conditions. If you need further help with that, the online help for AF is quite extensive.
Metric-Filter.xlsx

Author

Commented:
This is exactly what I was looking for!

So happy to not have a pivot table or the extra column, and the macro recorded vba (which I will make dynamic) is so terse.

Thank you so much!!
Rob HensonFinance Analyst

Commented:
Glad to help.