search in matrix.xlsx | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | col 1 | col 2 | col 3 | search | celaddress | ||||
2 | A | B | B | C | $C$5 | Row | 5 | ||
3 | B | D | D | Column | 3 | ||||
4 | B | D | E | ||||||
5 | D | E | C | ||||||
6 | E | F | A | ||||||
Blad1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E2 | E2 | =IF(COUNTIF(A2:C6,D2)=0,"Not Found",IF(COUNTIF(A2:C6,D2)>1,0,ADDRESS(MAX(INDEX(ROW(A2:C6)*(A2:C6=D2),0)),MAX(INDEX(COLUMN(A2:C6)*(A2:C6=D2),0))))) |
G2 | G2 | =MAX(INDEX(ROW(A2:A6)*(A2:C6=D2),0)) |
G3 | G3 | =MAX(INDEX(COLUMN(A2:C2)*(A2:C6=D2),0)) |
Book1 | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
1 | Crit col 1 | Crit col 2 | Crit col 3 | Result Column | Lookup criteria | Result | ||||
2 | 69 | 63 | 76 | 197 | 46 | 180 | ||||
3 | 97 | 44 | 22 | 193 | ||||||
4 | 90 | 69 | 98 | 163 | ||||||
5 | 85 | 51 | 52 | 124 | ||||||
6 | 36 | 36 | 41 | 145 | ||||||
7 | 78 | 69 | 66 | 125 | ||||||
8 | 43 | 62 | 95 | 163 | ||||||
9 | 70 | 46 | 57 | 180 | ||||||
10 | 87 | 50 | 15 | 123 | ||||||
11 | 99 | 77 | 31 | 154 | ||||||
12 | 51 | 85 | 95 | 139 | ||||||
13 | 54 | 33 | 12 | 117 | ||||||
14 | 62 | 53 | 63 | 136 | ||||||
15 | 50 | 97 | 87 | 155 | ||||||
16 | 86 | 48 | 59 | 187 | ||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
H2 | H2 | =INDEX($E$2:$E$16,MATCH(1,MMULT(--($A$2:$C$16=G2),TRANSPOSE(COLUMN($A$2:$C$16)^0)),0)) |
Press CTRL+SHIFT+ENTER to enter array formulas. |
Product Costing Master 251120.xlsx | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | F | G | H | ||||
1 | Mince Giv | Mince Giv | ||||||||
2 | COS | Raw Material Code | Description 1 | Description 2 | Cost/kg | MFPC050.V1 | MFPC048 | |||
3 | Ingredients | RM008 | 554M - EXCEL FIT HI-CEL 554M (METHYL CELLULOSE SOLUTION) | 10.95 | 0.1752 | #N/A | ||||
4 | Ingredients | RM011.001 | SUPRO 500 - SOYA PROTEIN ISOLATE - SUPRO 500 | Soya protein | 2.95 | |||||
CoS FLAT |
Cell Formulas | ||
---|---|---|
Range | Formula | |
G3 | G3 | =SUMIFS(INDEX(INDIRECT("'"&G$1&"'!$C$8:$Y$100"),0,MATCH(G$2,INDIRECT("'"&G$1&"'!$C90:$Y90"),0)),INDIRECT("'"&G$1&"'!$B$8:$B$100"),$C3) |
H3 | H3 | =SUMIFS(INDEX(INDIRECT("'"&H$1&"'!$C$8:$Y$100"),0,MATCH(H$2,INDIRECT("'"&H$1&"'!$C90:$Y90")&INDIRECT("'"&H$1&"'!$C91:$Y91")&INDIRECT("'"&H$1&"'!$C92:$Y92")&INDIRECT("'"&H$1&"'!$C93:$Y93")&INDIRECT("'"&H$1&"'!$C94:$Y94")&INDIRECT("'"&H$1&"'!$C95:$Y95"),0)),INDIRECT("'"&H$1&"'!$B$8:$B$100"),$C3) |
Product Costing Master 251120.xlsx | |||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | |||
88 | |||||||||||||||
89 | |||||||||||||||
90 | Product codes | MFPC050.V1 | MFPC048 | ||||||||||||
91 | MFPC057 | ||||||||||||||
92 | MFPC131 | ||||||||||||||
93 | MFPC060 | ||||||||||||||
94 | MFPC104 | ||||||||||||||
95 | MFPC106 | ||||||||||||||
96 | |||||||||||||||
97 | |||||||||||||||
98 | |||||||||||||||
Mince Giv |
CoS FLAT | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | F | G | H | |||
Product Costing Master 251120.xlsx | ||||||||
1 | Mince Giv | Mince Giv | ||||||
2 | COS | Raw Material Code | Description 1 | Cost/kg | MFPC050.V1 | MFPC048 | ||
3 | Ingredients | RM008 | 554M - EXCEL FIT HI-CEL 554M (METHYL CELLULOSE SOLUTION) | 10.95 | 0.1752 | - | ||
4 | Ingredients | RM011.001 | SUPRO 500 - SOYA PROTEIN ISOLATE - SUPRO 500 | 2.95 | 0.2643 | - | ||
5 | Ingredients | RM014 | R00003 - REFINED RAPESEED OIL | 1.233696 | 0.1875 | - |
Range | Formula | |
---|---|---|
Cell Formulas | ||
G3:G5 | G3 | |
H3:H5 | H3 | =SUMIFS(INDEX(INDIRECT("'"&H$1&"'!$C$8:$Y$100"),0,MATCH(1,MMULT(--(INDIRECT("'"&H$1&"'!$C90:$Y95")=H$2),TRANSPOSE(COLUMN(INDIRECT("'"&H$1&"'!$C90:$Y95"))^0)),0)),INDIRECT("'"&H$1&"'!$B$8:$B$100"),$C3) |
Mince Giv | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | H | I | J | K | L | |
Product Costing Master 251120.xlsx | |||||||||||
88 | |||||||||||
89 | |||||||||||
90 | Product codes | MFPC050.V1 | MFPC048 | ||||||||
91 | MFPC057 | ||||||||||
92 | MFPC131 | ||||||||||
93 | MFPC060 | ||||||||||
94 | MFPC104 | ||||||||||
95 | MFPC106 |