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)

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

7

Allergen Egg

Allergen Milk

Allergen Wheat

Allergen Buckwheat

Allergen Peanuts

Allergen Shrimp / prawn

Allergen Crab

8

Item Code

Supplier

Item

Unit

Std.Cost

QTY (g)

Act Qty

Act Cost

Energy

Stat

9

1

50012

Sauce Curry

No

Yes

Yes

No

No

No

No

10

2

60010

Candy

Traces of

Yes

No

No

No

No

No

11

3

40013

Milk Long Life

No

Yes

No

No

No

No

No

12

4

30011

Rice

No

No

No

No

No

No

No

13

5

20014

Bread

Traces of

Traces of

Yes

No

No

No

No

14

6

15

7

16

8

17

9

18

10

19

11

20

12

21

13

22

14

23

15

24

***

**

***

********

********

*************

*****

********

*******

*******

********

*******

****

*********

*********

*********

***********

*********

****************

*********

**

<tbody>
</tbody>

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.