View original (French)

Index+Equiv with 3 criteria

CCB91 -  
 soignisec -
Hello,

I want to use the Index + Match function with 3 criteria but it doesn't work

=INDEX(PrixNet;MATCH(1;(Fournisseurs=B24)*(Produits=D24)*(CodeFourn=C24);0))

Actually, I have two files:
In the first one, I have 4 columns

Supplier Product Code Price
Unilever Coca AB1 1.2
Unilever CocaZero AB1 1.03

So since I sometimes have the same supplier and the same code, I need to look up 3 criteria to extract the price in my new file.
However, the formula I entered does not work. It works with two criteria but not with three.

Can anyone help me?

Thank you in advance

Configuration: Windows XP / Internet Explorer 7.0

2 answers

  1. soignisec
     
    Hello,

    After extensive research on various forums, particularly
    http://www.developpez.net/forums/d450036/logiciels/microsoft-office/excel/excel-utiliser-index-equiv-2-conditions/

    I arrived, in the case of 3 or more solutions, at the following formula:
    INDEX(J$4:J$11;MATCH($B2;IF($D2=$D$4:$D$11;IF($E2=$E$4:$E$11;IF($F2=$F$4:$F$11;IF($G2=$G$4:$G$11;IF($H2=$H$4:$H$11;IF($I2=$I$4:$I$11;IF($C2=$C$4:$C$11;IF($K2=$K$4:$K$11;IF($L2=$L$4:$L$11;$B$4:$B$11;"";"");"";"");"";"");"";"");"";"");"";""));0)

    by pressing ctrl + shift + enter, Excel adds brackets and displays the following formula if you return to the cell:
    {=INDEX(J$4:J$11;MATCH($B2;IF($D2=$D$4:$D$11;IF($E2=$E$4:$E$11;IF($F2=$F$4:$F$11;IF($G2=$G$4:$G$11;IF($H2=$H$4:$H$11;IF($I2=$I$4:$I$11;IF($C2=$C$4:$C$11;IF($K2=$K$4:$K$11;IF($L2=$L$4:$L$11;$B$4:$B$11;"";"");"";"");"";"");"";"");"";"");"";""));0)}

    Translated in your case, it would look like this:
    {=INDEX("price field";MATCH("Supplier";IF("product"="product field";IF("criterion 3"="criterion zone 3";IF("criterion 4"="criterion zone 4";"supplier field";"");"");"");""));0)}

    When a value is not correct, the search column of MATCH becomes filled with "" and returns nothing.
    When a value is found, in array form we do get the result.

    I find the formula very powerful and wanted to share it.

    Best regards,
    4