Question : Multiple Criteria for Data Validation

Hi,

I'm new on this forum. I have a problem in getting rows from a multiple criteria. Below is my source table:

Sheet 1











Sheet 2:

I have 3 cells A, B and C.

A is for Resource and B is for Region. If I select DBA in cell A and Asia in cell B, then David and William will be added on my dropdown list for cell C. Someting like this:








Please help me in using OFFSET and MATCH functions for this.

Thank you in advance.

In Sheet 1, create another column, say column K to list the names for data validation population, and insert formula in K2:

=IFERROR(INDEX($C$2:$C$7,SMALL(IF($A$2:$A$7=Sheet2!$A$2,IF($B$2:$B$7=Sheet2!$B$2,ROW($A$2:$A$7)-ROW($A$2)+1)),ROWS($K$2:$K2))),"")

confirmed with CTRL+SHIFT+ENTER not just ENTER. Then copy down as far as the original Name list.

Now name this column, Name. Do this by select column K and typing the word Name in the Name Box just to the left of the formula bar.

Now for the Data Validation in Sheet2. Use List and formula:

=OFFSET(Name,1,0,COUNTIF(Name,"?*")-1,1)