IF and INDEX & MATCH formula help

[k3v1n23]

Hi Guys,

Need a bit of help with this formula please:

=IF(AND(A3="Product 1",B3<=49),B3*F3*(INDEX('Stock Prices'!$B$2:$B$65,MATCH('Company Inventory'!D3,'Stock Prices'!$A$2:$A$65,0),IF(AND(A3="Product 1",B3>=50),B3*F3*(INDEX('Stock Prices'!$C$2:$C$65,MATCH('Company Inventory'!D3,'Stock Prices'!$A$2:$A$65,0)))))))

It gives me an answer no problem when the first IF criteria is met i.e. if A3 equals Product 1 and B3 is less than 49. However, when it comes to criteria two i.e. if A3 equals Product 1 and B3 is greater than or equal to 50 then it says FALSE. Can't understand where I've gone wrong.

Any help would be much appreciated!


Thanks,

Kev

 

[Aladin Akyurek]

Does this re-write...

=IF(A3="Product 1",B3*F3*INDEX('Stock Prices'!$B$2:$C$65,MATCH('Company Inventory'!D3,'Stock Prices'!$A$2:$A$65,0),IF(B3<=49,1,2)),"")

work for you?

 

[k3v1n23]

Hi,

Thanks for your reply. I stayed up very late and somehow figured it out.

=IF(AND(A7="Vinyl",B7<=49),B7*F7*(INDEX('Stock Prices'!$B$2:$B$65,MATCH('Black Lotus Inventory'!D7,'Stock Prices'!$A$2:$A$65,0))),IF(AND(A7="Vinyl",B7>=50),B7*F7*(INDEX('Stock Prices'!$C$2:$C$65,MATCH('Black Lotus Inventory'!D7,'Stock Prices'!$A$2:$A$65,0)))))

I added two extra parenthesis at the end of the initial MATCH function. It worked, but not sure how?

 

[Aladin Akyurek]

Hi,

Thanks for your reply. I stayed up very late and somehow figured it out.

=IF(AND(A7="Vinyl",B7<=49),B7*F7*(INDEX('Stock Prices'!$B$2:$B$65,MATCH('Black Lotus Inventory'!D7,'Stock Prices'!$A$2:$A$65,0))),IF(AND(A7="Vinyl",B7>=50),B7*F7*(INDEX('Stock Prices'!$C$2:$C$65,MATCH('Black Lotus Inventory'!D7,'Stock Prices'!$A$2:$A$65,0)))))

I added two extra parenthesis at the end of the initial MATCH function. It worked, but not sure how?

Did you also consider the shorter version?