Hello,
It's really hard to try to explain what I want (in my head it's so clear ? ) In a previous post I asked for a formula to search for the biggest value equal to cell B4.
Now I would like to do the following:
B13 has the output of the biggest value. I would like to search for that output in cell A21:B31 and tell me which category is equal to that value, but only look/search for the values equal to B4
Example: Here he can only choose between A, F & K to see wich one equals 12.
If multiple correct options just take it in alphabetic order.
It's really hard to try to explain what I want (in my head it's so clear ? ) In a previous post I asked for a formula to search for the biggest value equal to cell B4.
Now I would like to do the following:
B13 has the output of the biggest value. I would like to search for that output in cell A21:B31 and tell me which category is equal to that value, but only look/search for the values equal to B4
Example: Here he can only choose between A, F & K to see wich one equals 12.
If multiple correct options just take it in alphabetic order.
TCA berekening.xlsm | ||||
---|---|---|---|---|
A | B | |||
3 | Parameters : ISO 22000:2018 | |||
4 | Categorieën?: | A, F, K, | ||
5 | ||||
6 | Aantal HACCP-plannen?: | |||
7 | Aantal FTE's?: | |||
8 | Extra site(s)?: | |||
9 | N/A | |||
10 | ||||
11 | Haalt comma's weg | A F K | ||
12 | Grootste CAT | |||
13 | Grootste CAT (uren) | 12 | ||
14 | ||||
15 | ||||
16 | ||||
17 | ||||
18 | ||||
19 | ||||
20 | Categorieën | Te besteden mandagen (uren) | ||
21 | A | 6 | ||
22 | B | 6 | ||
23 | C | 12 | ||
24 | D | 12 | ||
25 | E | 8 | ||
26 | F | 8 | ||
27 | G | 8 | ||
28 | H | 8 | ||
29 | I | 8 | ||
30 | J | 8 | ||
31 | K | 12 | ||
ISO22000 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
A9 | A9 | =IF(B8="Ja",'ISO22000'!$F$36,"N/A") |
B11 | B11 | =SUBSTITUTE(B4,",","") |
B13 | B13 | =AGGREGATE(14,6,(SEARCH('ISO22000'!$A$21:$A$31,B4)>0)*('ISO22000'!$B$21:$B$31),1) |
Named Ranges | ||
---|---|---|
Name | Refers To | Cells |
ISOOutput | ='ISO22000'!$B$4 | B13, B11 |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
B6 | List | =HACCPISO22000 |
B7 | List | =FTEISO22000 |
B8 | List | =ExtraSiteISO |
B9 | List | =AantalSitesISO |