Data validation list > multiple criteria

[gwstudent]

Hi experts -

Is it possible to display a data validation list based on multiple (2) criteria? Using the data below as an example, suppose I want to display a data validation list based on Part = 3 and Code = A. The resulting data validation list should be cat, dog, cow. Similarly, if Part = 4 and Code = C the resulting data validation list should be goat, fish.

A1: Part B1: Code C1: Description
A2: 3 B2: A C2: cat
A3: 3 B3: A C3: dog
A4: 3 B4: A C4: cow
A5: 3 B5: B C5: chicken
A6: 3 B5: B C6: lamb
A7: 4 B5: C C7: goat
A8: 4 B5: C C8: fish
A9: 6 B5: C C9: horse

I have found this easy enough if there is a single criteria but adding a second criteria has proven challenging.
=OFFSET(Part,MATCH("3",$A2:$A9,0)-1,2,COUNTIF($A2:$A9,"3"),1)

Any help would be appreciated.

 

[azumi]

=OFFSET($C$1,MATCH(3&"A",$A$2:$A$9&$B$2:$B$9,0),0,COUNTIFS($A$2:$A$9,3,$B$2:$B$9,"A"))

 

[gwstudent]

Azumi -

This works if the data is on the same page. I can't get this to work if the data is on two separate worksheets. For example, the Part = 3 and Code = A information is on one worksheet and the description is on another. I get an #N/A message. I'm using named ranges also. Any ideas?

 

[gwstudent]

Scratch that. I got it. Thanks Azumi - you rule!