Index Match with multiple conditions combined with an IF statement

[MarkR3003]

I have a workbook that contains three validation cells, 'Brand'; which has two values, 'Customer' and 'Product', both which have multiple values. Two seperate datasheets (tabs) for each 'Brand' contain the 'lookup' data, both containing columns for 'Customer', 'Product' and 'Discount'

Im trying to get the formula below to return the 'Discount' value from the appropriate 'Brand' tab when the selected 'Brand',

'Customer' and 'Product'​

validation cells are matched

{=IF(B8="Brand_A",(INDEX('Discount_1'!P:P,MATCH($G$3&V8,'Discount_1'!A:A&'Discount_1'!C:C,0),IF(B8="Brand_B",(INDEX('Discount_2'!O:O,MATCH($G$3&D8,'

Discount_2​

'!A:A&'

Discount_2​

'!B:B,0)))))))}

B8 = Validation cell containing either "Brand_A" or "Brand_B"
$G$3 = Validation cell containing "Customer" list
V8 = "Brand_A" product
D8 = "Brand_B" product
Discount_1 = Tab containing huge data table for "Brand_A" with Column A (Customer) and Column C (Product)

Discount_2 = Tab containing huge data table for "Brand_B" with Column A (Customer) and Column B (Product)
Discount_1 ​

P:P = Column containing the discount value to be returned
Discount_2 O:O =

Column containing the discount value to be returned​

I have made the formula into an array which returns the correct discount value when "Brand_A" is selected in B8, but only returns "FALSE" when "Brand_B" is selected in B8

Grateful if you could advise what I am doing wrong?

Many thanks

 

[Fluff]

Hi & welcome to MrExcel.
Try
=IF(B8="Brand_A",INDEX('Discount_1'!P:P,MATCH($G$3&V8,'Discount_1'!A:A&'Discount_1'!C:C,0)),IF(B8="Brand_B",INDEX('Discount_2'!O:O,MATCH($G$3&D8,'Discount_2'!A:A&'Discount_2'!B:B,0))))

 

[MarkR3003]

Many thanks Fluff for the quick reply. The formula works perfectly. Brilliant!

 

[Fluff]

You're welcome & thanks for the feedback

 

[Mr_Lucky]

I am using this formula IFERROR(IF(INDEX(E3:E7,MATCH(H3,D3:D7,0))="Unscheduled", "",INDEX(E3:E7,MATCH(H3,D3:D7,0))),"") and I want to an another condition to handle if the reference cell is blank.

But cant get it to work with first condition "Unscheduled". Is there a way to add IFERROR(IF(INDEX(E3:E7,MATCH(H3,D3:D7,0))=0,"", to IFERROR(IF(INDEX(E3:E7,MATCH(H3,D3:D7,0))="Unscheduled", "",INDEX(E3:E7,MATCH(H3,D3:D7,0))),"").


[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Schedule[/TD]
[TD]Expected result[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]19.16.0[/TD]
[TD]19.16.0[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Unscheduled[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]19.16.2[/TD]
[TD]19.16.2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]19.16.3[/TD]
[TD]19.16.3[/TD]
[/TR]
</tbody>[/TABLE]

Any help would be appreciated

 

[Fluff]

How about
=IFERROR(IF(OR(INDEX(E3:E7,MATCH(H3,D3:D7,0))={"Unscheduled",0}), "",INDEX(E3:E7,MATCH(H3,D3:D7,0))),"")

 

[Mr_Lucky]

That worked perfectly.

Many thanks for your help

 

[Fluff]

My pleasure & thanks for the feedback