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(A25:A35;AGGREGAAT(15;6;(VIND.SPEC(A25:A35;B4)>0)*(B25:B35=B12)*(RIJ(A25:A35)-24);1))&" "&ALS.FOUT(INDEX(A25:A35;AGGREGAAT(15;6;(VIND.SPEC(A25:A35;B4)>0)*(B25:B35=B12)*RIJ(A25:A35)-24;2));"")&" "&ALS.FOUT(INDEX(A25:A35;AGGREGAAT(15;6;(VIND.SPEC(A25:A35;B4)>0)*(B25:B35=B12)*RIJ(A25:A35)-24;3));"")

 

[mart37]

=ALS.FOUT(INDEX(A25:A35;AGGREGAAT(15;6;((1/((VIND.SPEC(A25:A35;B4))*(B25:B35=B12))>0))*(RIJ(A25:A35)-24);1));"")&" "&ALS.FOUT(INDEX(A25:A35;AGGREGAAT(15;6;((1/((VIND.SPEC(A25:A35;B4))*(B25:B35=B12))>0))*(RIJ(A25:A35)-24);2));"")&" "&ALS.FOUT(INDEX(A25:A35;AGGREGAAT(15;6;((1/((VIND.SPEC(A25:A35;B4))*(B25:B35=B12))>0))*(RIJ(A25:A35)-24);3));"")

 

[DarkSmile]

=ALS.FOUT(INDEX(A25:A35;AGGREGAAT(15;6;((1/((VIND.SPEC(A25:A35;B4))*(B25:B35=B12))>0))*(RIJ(A25:A35)-24);1));"")&" "&ALS.FOUT(INDEX(A25:A35;AGGREGAAT(15;6;((1/((VIND.SPEC(A25:A35;B4))*(B25:B35=B12))>0))*(RIJ(A25:A35)-24);2));"")&" "&ALS.FOUT(INDEX(A25:A35;AGGREGAAT(15;6;((1/((VIND.SPEC(A25:A35;B4))*(B25:B35=B12))>0))*(RIJ(A25:A35)-24);3));"")

This one works! Here is the translation if someone ever needs it:

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

Also, this works too for what I was trying (found it later than your reply)

Shows Cat (F)

=INDEX(CatISO22000,MATCH(H3,(ISNUMBER(SEARCH(CatISO22000,ISOOutput2))*(B21:B31)),0))

Shows Number (12)

=MAX(ISNUMBER(SEARCH(CatISO22000,ISOOutput2))*$B$21:$B$31)