I am trying to right a formula that will An example may explain better:

I have a table with the (Products) following in Row order, say A1 through
A4. Each one of these has its own code depending on where they are purchased
from

Apples
Oranges
Bananas
Pears

These can be purchased from a large number of Location. For simple example
purposes I will just have 2 locations:

New York - the code for which can either be NY01X or NY01 depending on the
product selected
London - the code for which can either be LN01X or NY01 depending on the
product selected

I have a Drop down list with the products listed. I choose one of the 4
products above and that should produce a code for me. It does to a point. I
can't get it to return a either **01 or **01X but not both using a set
formula in the cell. This is required because any product can be selected.

Apples in New York should have the code 2000-NY01
Oranges in New York should have the code 2050-NY01X
Pears in London should have the code 2100-LN01
Pears in New York should have the code 2100-LN01X

My attempt at the fomula was the following: - please ignore actual
references in this formula as its from actual data:

=IF(C11=0," ",INDEX(Misc!$I$47:$K$75,MATCH($C11,Misc!$I$47:$I$ 75),MATCH("Sub
Account",Misc!$I$47:$K$47,)))


In this C11 is the location where 'Sub Account' lists all the codes with
**01. I47:K47 is the whole array where all codes are displayed

My second attempt was to try and combine both 'SubAccount' list and the list
the details the values **01X by the following-

=IF(K10=14100,(INDEX(Misc!$I$47:$L$75,MATCH($C10,M isc!$I$47:$I$75),MATCH("Su
b L3",Misc!$I$47:$L$47,)))),IF(C10=0,"
",INDEX(Misc!$I$47:$K$75,MATCH($C10,Misc!$I$47:$I$ 75),MATCH("Sub
Account",Misc!$I$47:$K$47,)))

But this only produces a #value. I tried to put an @if statement at the
start, this returns the correct value but only if the second @if statement
that is in the formula shown is not put in.

Sorry if this seems rather complicated

Thanks

John