Index and Match with multiple criterias

[pingvin]

Hi,
I have a problem with a Index and Match formula with multiple criteria (array formula).
I have tried two different versions but neither of them work, could anybody please have a look at this? I have also uploaded the file to my dropbox if you want to see the real file.

Version 1: {=INDEX('Allergy & Calories'!$A$10:$I$15,MATCH($C9&M$7,'Allergy & Calories'!$A$10:$A$15&'Allergy & Calories'!$C$10:$C$15,0))}
Version 2: {=INDEX('Allergy & Calories'!$C$10:$I$15,MATCH(1,('Allergy & Calories'!$A$10:$A$15=$C$9)*('Allergy & Calories'!$C$10:$I$15=N$7),0))}

I want the have the value from cell $C$10:$I$15 on the "Allergy & Calories worksheet" into cell $M$9:$S$23 on the "6 short Recipes sheet".
But only if the criteria matches the item code in cells $C$9:$C$23 and the names in cells $M$7:$S$7 on the "6 short Recipes sheet".

https://www.dropbox.com/s/izaqxtmm52tk1gp/array formula (test).xlsx?dl=0

 

[Aladin Akyurek]

In M9 enter, copy across, and down:

=IFERROR(INDEX('Allergy & Calories'!$C$11:$I$15,MATCH($C9,'Allergy & Calories'!$A$11:$A$15,0),MATCH("*"&M$7&"*",'Allergy & Calories'!$C$10:$I$10,0)),"")

 

[azumi]

Hope this helps, just open this external link and cheeeers

https://simpan.ugm.ac.id/s/HFjgHEbPWyJkNVA

 

[markmzz]

Hi!

Try the Array Formula below in M9 and copy down and to the right.

Use Ctrl+Shift+Enter to enter the formula.

=VLOOKUP($E9,IF('Alergy & Calories'!$A$2:$A$6=$C9,'Alergy & Calories'!$B$2:$I$6),MATCH(M$7,'Alergy & Calories'!$B$1:$I$1,0),0)


[TABLE="class: grid, width: 1249"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]Q[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]S[/TD]
[TD="align: center"]T[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Allergen Egg[/TD]
[TD="align: center"]Allergen Milk[/TD]
[TD="align: center"]Allergen Wheat[/TD]
[TD="align: center"]Allergen Buckwheat[/TD]
[TD="align: center"]Allergen Peanuts[/TD]
[TD="align: center"]Allergen Shrimp / prawn[/TD]
[TD="align: center"]Allergen Crab[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Item Code[/TD]
[TD="align: center"]Supplier[/TD]
[TD="align: center"]Item[/TD]
[TD="align: center"]Unit[/TD]
[TD="align: center"]Std.Cost[/TD]
[TD="align: center"]QTY (g)[/TD]
[TD="align: center"]Act Qty[/TD]
[TD="align: center"]Act Cost[/TD]
[TD="align: center"]Energy[/TD]
[TD="align: center"]Stat[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]50012[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Sauce Curry[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]60010[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Candy[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Traces of[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"][/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]40013[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Milk Long Life[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"][/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]30011[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Rice[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]20014[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Bread[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Traces of[/TD]
[TD="align: center"]Traces of[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: center"][/TD]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: center"][/TD]
[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD="align: center"][/TD]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD="align: center"][/TD]
[TD="align: center"]9[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="align: center"][/TD]
[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]19[/TD]
[TD="align: center"][/TD]
[TD="align: center"]11[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]20[/TD]
[TD="align: center"][/TD]
[TD="align: center"]12[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]21[/TD]
[TD="align: center"][/TD]
[TD="align: center"]13[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]22[/TD]
[TD="align: center"][/TD]
[TD="align: center"]14[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]23[/TD]
[TD="align: center"][/TD]
[TD="align: center"]15[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]24[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]***[/TD]
[TD="align: center"]**[/TD]
[TD="align: center"]***[/TD]
[TD="align: center"]********[/TD]
[TD="align: center"]********[/TD]
[TD="align: center"]*************[/TD]
[TD="align: center"]*****[/TD]
[TD="align: center"]********[/TD]
[TD="align: center"]*******[/TD]
[TD="align: center"]*******[/TD]
[TD="align: center"]********[/TD]
[TD="align: center"]*******[/TD]
[TD="align: center"]****[/TD]
[TD="align: center"]*********[/TD]
[TD="align: center"]*********[/TD]
[TD="align: center"]*********[/TD]
[TD="align: center"]***********[/TD]
[TD="align: center"]*********[/TD]
[TD="align: center"]****************[/TD]
[TD="align: center"]*********[/TD]
[TD="align: center"]**[/TD]
[/TR]
</tbody>[/TABLE]

Markmzz

 

[pingvin]

Thanks for the help guys!
Is there also possible have the values in $M$9:$S$9 changed?
Instead of showing "No", to have it show ":"
Instead of showing "Yes", to have it show "X"
Instead of showing "Traces of", to have it show "T"
Is this possible by applying conditional formatting to cell $M$9:$S$9

 

[Aladin Akyurek]

Thanks for the help guys!
Is there also possible have the values in $M$9:$S$9 changed?
Instead of showing "No", to have it show ":"
Instead of showing "Yes", to have it show "X"
Instead of showing "Traces of", to have it show "T"
Is this possible by applying conditional formatting to cell $M$9:$S$9

Why not? These are user entries. If you change them, the formula will invariably use them.

If you don't want to change the user entries, but require the corresponding new values...

In M9 enter, copy across, and down:

=IFERROR(VLOOKUP(INDEX('Allergy & Calories'!$C$11:$I$15,MATCH($C9,'Allergy & Calories'!$A$11:$A$15,0),MATCH("*"&M$7&"*",'Allergy & Calories'!$C$10:$I$10,0)),{"no",":";"yes","X";"traces of","T"},2,0),"")

 

[markmzz]

Thanks for the help guys!
Is there also possible have the values in $M$9:$S$9 changed?
Instead of showing "No", to have it show ":"
Instead of showing "Yes", to have it show "X"
Instead of showing "Traces of", to have it show "T"
Is this possible by applying conditional formatting to cell $M$9:$S$9

Hi Pingvin,

Lets go:

1) In this case, try this:

=VLOOKUP(VLOOKUP($E9,IF('Alergy & Calories'!$A$2:$A$6=$C9,'Alergy & Calories'!$B$2:$I$6),MATCH(M$7,'Alergy & Calories'!$B$1:$J$1,0),0),
{"No",":";"Yes","X";"Traces of","T"},2,0)

or

=LOOKUP(VLOOKUP($E9,IF('Alergy & Calories'!$A$2:$A$6=$C9,'Alergy & Calories'!$B$2:$I$6),MATCH(M$7,'Alergy & Calories'!$B$1:$J$1,0),0),
{"No",":";"Traces of","T";"Yes","X"})

2) Yes, is possible. What conditional formatting do you want? What rules?

Markmzz

 

[markmzz]

=LOOKUP(VLOOKUP($E9,IF('Alergy & Calories'!$A$2:$A$6=$C9,'Alergy & Calories'!$B$2:$I$6),MATCH(M$7,'Alergy & Calories'!$B$1:$J$1,0),0),
{"No",":";"Traces of","T";"Yes","X"})

Hi again!

I think that the second Array Formula of my previous post, isn't ok.

Instead, try this:

Use Ctrl+Shift+Enter to enter the formula

=LOOKUP(1,1/({"No";"Traces of";"Yes"}=VLOOKUP($E9,IF('Alergy & Calories'!$A$2:$A$6=$C9,
'Alergy & Calories'!$B$2:$I$6),MATCH(M$7,'Alergy & Calories'!$B$1:$J$1,0),0)),{":";"T";"X"})

Markmzz

 

[pingvin]

Thanks everyone for the help it really helped me alot.