Utilising Validation List results

[julhs]

Hopefully the Xl2bb I have uploaded will give a rough sense of my layout and what I am trying to do,
along with the formula (Xl2bb doesn’t seem to display Validation List drop downs)
But even if it did, my Validation Lists are dependent on an External source workbook and not really relevant to this query.

At the moment in D11 & D12 I have Index Match formulas that pull the relevant data from the source book,
dependent on the selection made in Validation List #3 (But for sake of simplicity we will just deal with D11).
I also have a second formula that does exactly the same, but this time for Validation List #2.
Both formula work in D11 (when on their own) and a selection is made in the relevant VD list. But I can’t find the right way to combine the 2 separate formulas for D11 that DOESN’T result in “#N/A”.

00 Fert-Chemical-Nozzle calculator NEW Currant.xlsm
	A	B	C	D	E	F	G	H	I
1
2
3
4
5
6					Data Validation List #1
7					Data Validation List #2 (Dependent on #1)
8
9					Data Validation List #3
10
11	Product container size
12		Product Cost
13
14
15			Formula for D11 that works with VD list #2				Enter product used
16			Formula for D11 that works with VD list #3				Enter product used
17
Sheet3

Cell Formulas
Range	Formula
G15	G15	=IF(AND(D7="",D9=""),"Enter product used",IF(AND(D7>"",D9=0,(ISERROR(INDEX('aaFert_&_Chem_Pricing.xlsm'!Chemical_Range,MATCH(D7,'aaFert_&_Chem_Pricing.xlsm'!Chemical_Name,0),1)))),"Chemical not found", IF(AND(D7>"",D9=0,INDEX('aaFert_&_Chem_Pricing.xlsm'!Chemical_Range,MATCH(D7,'aaFert_&_Chem_Pricing.xlsm'!Chemical_Name,0),3)+INDEX('aaFert_&_Chem_Pricing.xlsm'!Chemical_Range,MATCH(D7,'aaFert_&_Chem_Pricing.xlsm'!Chemical_Name,0),4)=0),"No size entry", IF(AND(D7>"",D9=0,INDEX('aaFert_&_Chem_Pricing.xlsm'!Chemical_Range,MATCH(D7,'aaFert_&_Chem_Pricing.xlsm'!Chemical_Name,0),4)=0),INDEX('aaFert_&_Chem_Pricing.xlsm'!Chemical_Range,MATCH(D7,'aaFert_&_Chem_Pricing.xlsm'!Chemical_Name,0),3)&" lts",IF(AND(D7>"",D9=0,INDEX('aaFert_&_Chem_Pricing.xlsm'!Chemical_Range,MATCH(D7,'aaFert_&_Chem_Pricing.xlsm'!Chemical_Name,0),3)=0),INDEX('aaFert_&_Chem_Pricing.xlsm'!Chemical_Range,MATCH(D7,'aaFert_&_Chem_Pricing.xlsm'!Chemical_Name,0),4)&" kg" )))))
G16	G16	=IF(AND(D7="",D9=""),"Enter product used",IF(AND(D9>"",D7=0,(ISERROR(INDEX('aaFert_&_Chem_Pricing.xlsm'!Chemical_Range,MATCH(D9,'aaFert_&_Chem_Pricing.xlsm'!Chemical_Name,0),1)))),"Chemical not found", IF(AND(D9>"",D7=0,INDEX('aaFert_&_Chem_Pricing.xlsm'!Chemical_Range,MATCH(D9,'aaFert_&_Chem_Pricing.xlsm'!Chemical_Name,0),3)+INDEX('aaFert_&_Chem_Pricing.xlsm'!Chemical_Range,MATCH(D9,'aaFert_&_Chem_Pricing.xlsm'!Chemical_Name,0),4)=0),"No size entry", IF(AND(D9>"",D7=0,INDEX('aaFert_&_Chem_Pricing.xlsm'!Chemical_Range,MATCH(D9,'aaFert_&_Chem_Pricing.xlsm'!Chemical_Name,0),4)=0),INDEX('aaFert_&_Chem_Pricing.xlsm'!Chemical_Range,MATCH(D9,'aaFert_&_Chem_Pricing.xlsm'!Chemical_Name,0),3)&" lts",IF(AND(D9>"",D7=0,INDEX('aaFert_&_Chem_Pricing.xlsm'!Chemical_Range,MATCH(D9,'aaFert_&_Chem_Pricing.xlsm'!Chemical_Name,0),3)=0),INDEX('aaFert_&_Chem_Pricing.xlsm'!Chemical_Range,MATCH(D9,'aaFert_&_Chem_Pricing.xlsm'!Chemical_Name,0),4)&" kg" )))))

 

[julhs]

This is them combined and when it can return "#N/A"

=IF(AND(D7="",D9=""),"Enter product used",IF(AND(D7>"",D9=0,(ISERROR(INDEX('aaFert_&_Chem_Pricing.xlsm'!Chemical_Range,MATCH(D7,'aaFert_&_Chem_Pricing.xlsm'!Chemical_Name,0),1)))),"Chemical not found", IF(AND(D7>"",D9=0,INDEX('aaFert_&_Chem_Pricing.xlsm'!Chemical_Range,MATCH(D7,'aaFert_&_Chem_Pricing.xlsm'!Chemical_Name,0),3)+INDEX('aaFert_&_Chem_Pricing.xlsm'!Chemical_Range,MATCH(D7,'aaFert_&_Chem_Pricing.xlsm'!Chemical_Name,0),4)=0),"No size entry", IF(AND(D7>"",D9=0,INDEX('aaFert_&_Chem_Pricing.xlsm'!Chemical_Range,MATCH(D7,'aaFert_&_Chem_Pricing.xlsm'!Chemical_Name,0),4)=0),INDEX('aaFert_&_Chem_Pricing.xlsm'!Chemical_Range,MATCH(D7,'aaFert_&_Chem_Pricing.xlsm'!Chemical_Name,0),3)&" lts",IF(AND(D7>"",D9=0,INDEX('aaFert_&_Chem_Pricing.xlsm'!Chemical_Range,MATCH(D7,'aaFert_&_Chem_Pricing.xlsm'!Chemical_Name,0),3)=0),INDEX('aaFert_&_Chem_Pricing.xlsm'!Chemical_Range,MATCH(D7,'aaFert_&_Chem_Pricing.xlsm'!Chemical_Name,0),4)&" kg",
IF(AND(D9>"",D7=0,(ISERROR(INDEX('aaFert_&_Chem_Pricing.xlsm'!Chemical_Range,MATCH(D9,'aaFert_&_Chem_Pricing.xlsm'!Chemical_Name,0),1)))),"Chemical not found", IF(AND(D9>"",D7=0,INDEX('aaFert_&_Chem_Pricing.xlsm'!Chemical_Range,MATCH(D9,'aaFert_&_Chem_Pricing.xlsm'!Chemical_Name,0),3)+INDEX('aaFert_&_Chem_Pricing.xlsm'!Chemical_Range,MATCH(D9,'aaFert_&_Chem_Pricing.xlsm'!Chemical_Name,0),4)=0),"No size entry", IF(AND(D9>"",D7=0,INDEX('aaFert_&_Chem_Pricing.xlsm'!Chemical_Range,MATCH(D9,'aaFert_&_Chem_Pricing.xlsm'!Chemical_Name,0),4)=0),INDEX('aaFert_&_Chem_Pricing.xlsm'!Chemical_Range,MATCH(D9,'aaFert_&_Chem_Pricing.xlsm'!Chemical_Name,0),3)&" lts",IF(AND(D9>"",D7=0,INDEX('aaFert_&_Chem_Pricing.xlsm'!Chemical_Range,MATCH(D9,'aaFert_&_Chem_Pricing.xlsm'!Chemical_Name,0),3)=0),INDEX('aaFert_&_Chem_Pricing.xlsm'!Chemical_Range,MATCH(D9,'aaFert_&_Chem_Pricing.xlsm'!Chemical_Name,0),4)&" kg" ))))))))))

 

[julhs]

As I didn’t get any response, just wondering if was due to a badly written query or you simply CAN NOT Index-Match two separate Validation list selections in the same formula?
Essentially all I’m trying to do in D11 is verify that the source book has a value in it for the selection made in either of the VD lists (#2 or #3).
I have experimented with numerous different approaches at combining G15 with G16, all of which still give me a #N/A value for whichever of the VD lists that come second in the argument.
Eg.
When the 1st argument is; IF(AND(D7>"",D9=0,….(rest of Index Match formula), returns expected value
and the 2nd argument is; IF(AND(D9>"",D7=0,….(rest of Index Match formula), returns #N/A.
Switch the order;
1st argument is; IF(AND(D9>"",D7=0,…… returns expected value
2nd argument is; IF(AND(D7>"",D9=0,….. returns #N/A.

Has anyone got any suggestions?