I have to write an Index & Match excel formula, containing multiple criteria!
I have 2 tables:
My formula should have the following structure:
if Mercedes exists in the first column and Mercedes exists in the fifth column and
if the code in the second column matches the code in the sixth column
then take the cost from the seventh column and put it in the fourth empty column:
If both conditions not met, then insert 0.
It worked when I used Index & Match with 1 criterion, as seen in the above table:
=(INDEX($G$2:$G$4,MATCH(E2,$A$2:$A$6,0),0))
G is cost column, E2 is value in the second brand column and A is, of course, the first brand column. To add multiple criteria I have tried:
=INDEX(G2:G6,MATCH(1,(A2=A2:A4)*(E2=E2:E4),0))
It does not work! (returns N/A)
Could you please tell me where I am wrong?
Thank you!
