Formula: Search for value in range, but equal to specific text

[DarkSmile]

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.

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

 

[mart37]

=INDEX(A21:A31,AGGREGATE(14,6,(SEARCH(A21:A31,B4)>0)*(B21:B31=B13)*ROW(A21:A31)-20,1))

 

[DarkSmile]

Doesn't seem to work.. lowered the cells in the mean time so here is an update, but I'm getting a #REF!

TCA berekening.xlsm
	A	B
3	Parameters : ISO 22000:2018
4	Categorieën?:	A, F, K,
5
6	Aantal HACCP-plannen?:	2
7	Aantal FTE's?:	50-79
8	Extra site(s)?:	Ja
9	Aantal extra site(s)?:
10
11	Haalt comma's weg	A F K
12	Grootste CAT	#REF!
13	Grootste CAT (uren)	12
14	HACCP (uren)	#N/A
15	Aantal extra haccps	2
16	HACCP x aantal	#N/A
17	TMS	2
18	FTE	8
19	Totaal 1ste site	#N/A
20	Extra sites	#N/A
21	Totaal auditduur:	#N/A
22	Totaal auditduur (per extra site)	#N/A
23
24
25	Categorieën	Te besteden mandagen (uren)
26	A	6
27	B	6
28	C	12
29	D	12
30	E	8
31	F	8
32	G	8
33	H	8
34	I	8
35	J	8
36	K	12
ISO22000

Cell Formulas
Range	Formula
A9	A9	=IF(B8="Ja",'ISO22000'!$F$41,"N/A")
B11	B11	=SUBSTITUTE(B4,",","")
B12	B12	=INDEX(A26:A36,AGGREGATE(14,6,(SEARCH(A26:A36,B4)>0)*(B26:B36=B13)*ROW(A26:A36)-20,1))
B13	B13	=AGGREGATE(14,6,(SEARCH('ISO22000'!$A$26:$A$36,B4)>0)*('ISO22000'!$B$26:$B$36),1)
B14	B14	=LOOKUP(1000,SEARCH($A$26:$A$36,B12),$C$26:$C$36)
B15	B15	=IF(B6>"2",B6-1,B6)
B16	B16	=IF(B15<"0",B14*B15,0)
B17	B17	=D26
B18	B18	=LOOKUP(1000,SEARCH($E$26:$E$36,B7),$F$26:$F$36)
B19	B19	=B13+B16+B17+B18
B20	B20	=IF(B8="Ja",B19/2,0)
B21:B22	B21	=B19
A22	A22	=IF(B8="Ja",F42,"N/A")

Named Ranges
Name	Refers To	Cells
ISOOutput	='ISO22000'!$B$4	B11:B13

Cells with Data Validation
Cell	Allow	Criteria
B6	List	=HACCPISO22000
B7	List	=FTEISO22000
B8	List	=ExtraSiteISO
B9	List	=AantalSitesISO

 

[mart37]

-20 --> -25

 

[mart37]

For more options repeat the formula and change the value for the nth position.

=IFERROR(INDEX(A21:A31,AGGREGATE(15,6,(SEARCH(A21:A31,B4)>0)*(B21:B31=B13)*(ROW(A21:A31)-20),1)),"")&" "&IFERROR(INDEX(A21:A31,AGGREGATE(15,6,(SEARCH(A21:A31,B4)>0)*(B21:B31=B13)*ROW(A21:A31)-20,2)),"")&" "&IFERROR(INDEX(A21:A31,AGGREGATE(15,6,(SEARCH(A21:A31,B4)>0)*(B21:B31=B13)*ROW(A21:A31)-20,3)),"")

 

[DarkSmile]

It may be just me.. but this works but gives the wrong output..

TCA berekening.xlsm
	A	B
3	Parameters : ISO 22000:2018
4	Categorieën?:	A, F, K,
5
6	Aantal HACCP-plannen?:	2
7	Aantal FTE's?:	50-79
8	Extra site(s)?:	Nee
9	N/A
10
11	Grootste CAT	J
12	Grootste CAT (uren)	12
13	HACCP (uren)	2
14	Aantal extra haccps	2
15	HACCP x aantal	4
16	TMS	2
17	FTE	8
18	Totaal 1ste site	26
19	Extra sites	0
20	Totaal auditduur:	26
21	N/A	0
22
23
24	Categorieën	Te besteden mandagen (uren)
25	A	6
26	B	6
27	C	12
28	D	12
29	E	8
30	F	8
31	G	8
32	H	8
33	I	8
34	J	8
35	K	12
ISO22000

Cell Formulas
Range	Formula
A9	A9	=IF(B8="Ja",'ISO22000'!$F$40,"N/A")
B11	B11	=INDEX(A25:A35,AGGREGATE(14,6,(SEARCH(A25:A35,B4)>0)*(B25:B35=B12)*ROW(A25:A35)-25,1))
B12	B12	=AGGREGATE(14,6,(SEARCH('ISO22000'!$A$25:$A$35,B4)>0)*('ISO22000'!$B$25:$B$35),1)
B13	B13	=LOOKUP(1000,SEARCH($A$25:$A$35,B11),$C$25:$C$35)
B14	B14	=IF(B6>"2",B6-1,B6)
B15	B15	=IF(B14<"0",B13*B14,0)
B16	B16	=D25
B17	B17	=LOOKUP(1000,SEARCH($E$25:$E$35,B7),$F$25:$F$35)
B18	B18	=B12+B15+B16+B17
B19	B19	=IF(B8="Ja",B18/2,0)
B20:B21	B20	=B18
A21	A21	=IF(B8="Ja",F41,"N/A")

Named Ranges
Name	Refers To	Cells
ISOOutput	='ISO22000'!$B$4	B11:B12

Cells with Data Validation
Cell	Allow	Criteria
B6	List	=HACCPISO22000
B7	List	=FTEISO22000
B8	List	=ExtraSiteISO
B9	List	=AantalSitesISO

 

[DarkSmile]

=IFERROR(INDEX(A21:A31,AGGREGATE(15,6,(SEARCH(A21:A31,B4)>0)*(B21:B31=B13)*(ROW(A21:A31)-20),1)),"")&" "&IFERROR(INDEX(A21:A31,AGGREGATE(15,6,(SEARCH(A21:A31,B4)>0)*(B21:B31=B13)*ROW(A21:A31)-20,2)),"")&" "&IFERROR(INDEX(A21:A31,AGGREGATE(15,6,(SEARCH(A21:A31,B4)>0)*(B21:B31=B13)*ROW(A21:A31)-20,3)),"")

Shows all the things

 

[mart37]

deleting a row --> ROW(A25:A35)-24 (first rownumber 25 - 24 = 1)

 

[mart37]

changing rows --> A21:A31 ==> A25:A35
etc.

 

[DarkSmile]

I'm sorry, but I'm a basic excel user.. can't seem to figure this one out... here what I tried

=IFERROR(INDEX(A25:A35,AGGREGATE(15,6,(SEARCH(A25:A35,B4)>0)*(B25:B35=B12)*(ROW(A25:A35)-20),1)),"")&" "&IFERROR(INDEX(A25:A35,AGGREGATE(15,6,(SEARCH(A25:A35,B4)>0)*(B25:B35=B12)*ROW(A25:A35)-20,2)),"")&" "&IFERROR(INDEX(A25:A35,AGGREGATE(15,6,(SEARCH(A25:A35,B4)>0)*(B25:B35=B12)*ROW(A25:A35)-20,3))*ROW(A25:A35)-24,"")