Brief description of the problem:

We're using the formula below with range names for data validation. We had
to use range names because of the argument limitation (30). The range names
for example are LIST1, LIST2, LIST3 and so on. In the example below, each of
the columns (lists) are dependent on the previous. So, depending on what you
select from the Sub-Cat drop-down, it will determine the Prodtype drop-down
list values.

The problem we're having is when the values from the list are the same in
columns Prodtype and Probtype (both contain the value "none". The issue
appears to be with the MATCH function where it's selecting the first value it
matches on exactly. Whenever the combination (Facilities, access, none) from
the validation lists is selected, we would expect the Probtype drop down list
to be "not required". But
instead, we're getting "none", which is incorrect.

Formula:

=INDIRECT("LIST"&MATCH(E7,producttype,0))

Mini example showing the problem:

Cat Sub-Cat Prodtype Probtype

Enquiry complaint none none
Enquiry hang-up none none
Facilities access none not required
Facilities closure none not required
IMA add none none