Function to return 2 columns based on 2 user selected criteria

**lax2734** · 04-06-2013, 07:14 PM

Hello,

I currently have a workbook which contains a list of days/times and names of people who are available on those days. Is there a set of functions that can find which of these people meet user specified criteria (2 drop down lists) and retrieve their information?

I have attached a copy of a sample workbook which contains the information I am trying to work with. Thank you in advance for any and all assistance!

**martindwilson** · 04-06-2013, 07:52 PM

you can do it like this
2 named ranges for validation dropdown and a helper column I put on the dropdown info sheet

**Tony Valko** · 04-06-2013, 11:00 PM

You don't need 2 drop downs. You can just use the column headers on the Data sheet as the selections for a single drop down list. And, this means you don't need the Drop Down sheet.

Here's your file:

TestnewExcelsheet(1).xlsx

The formula in column B is an array formula.

Array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

**AZ-XL** · 04-07-2013, 05:13 AM

Hi
=INDEX(Data!$A$2:$A$8,MATCH(SMALL(IF(INDEX(Data!$D$2:$R$8,0,MATCH(TRUE,SEARCH($C$3,Data!$D$1:$R$1)*VALUE(SUBSTITUTE(Data!$D$1:$R$1,switchboard!$C$3,""))=$D$3,0))="yes",ROW(Data!$A$2:$A$8)-1,""),ROW(A1)),IF(INDEX(Data!$D$2:$R$8,0,MATCH(TRUE,SEARCH($C$3,Data!$D$1:$R$1)*VALUE(SUBSTITUTE(Data!$D$1:$R$1,switchboard!$C$3,""))=$D$3,0))="yes",ROW(Data!$A$2:$A$8)-1,""),0)) Ctrl+shift+enter

it doent use helper column but it shows NUM error if there are no more "yes" criteria. I couldnt use Iferror function Because it said there are too much nested function. Actually its first time happening to me.

**lax2734** · 04-07-2013, 10:39 AM

I cannot thank you all enough, I am working through each of the functions sent to see which makes it easiest for the user. You saved me a ton of time and a few headaches trying to piece functions together!