# Formula to search biggest number in range

#### DarkSmile

##### New Member
Hello,

I got the following setup (see below), in B5 you have a dropdown menu with a VBA code that allows to select multiple items. Those selected items will be shown in B4.
I'm trying to do the following: Look in B4 and search for the biggest number equal to that selection in range A21:B31

I also would like a formula that says "X" is the biggest category

TCA berekening.xlsm
AB
3Parameters : ISO 22000:2018
4Categorieën?: A, C, F,
5
6Aantal HACCP-plannen?:
7Aantal FTE's?:
8Extra site(s)?:
9N/A
10
11Cat:8
12
13
14
15
16
17
18
19
20Dropdown list: CategorieënTe besteden mandagen (uren)
21A6
22B6
23C12
24D12
25E8
26F8
27G8
28H8
29I8
30J8
31K12
ISO22000
Cell Formulas
RangeFormula
A9A9=IF(B8="Ja",'ISO22000'!\$F\$36,"N/A")
B11B11=LOOKUP(1000,SEARCH('ISO22000'!\$A\$21:\$A\$31,B4),'ISO22000'!\$B\$21:\$B\$31)
Named Ranges
NameRefers ToCells
ISOOutput='ISO22000'!\$B\$4B11
Cells with Data Validation
CellAllowCriteria
B6List=HACCPISO22000
B7List=FTEISO22000
B8List=ExtraSiteISO
B9List=AantalSitesISO

In this it example it should show 12 in B11

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

#### mart37

##### Well-known Member
Excel Formula:
``=AGGREGATE(14,6,(SEARCH('ISO22000'!\$A\$21:\$A\$31,B4)>0)*('ISO22000'!\$B\$21:\$B\$31),1)``

#### DarkSmile

##### New Member
Excel Formula:
``=AGGREGATE(14,6,(SEARCH('ISO22000'!\$A\$21:\$A\$31,B4)>0)*('ISO22000'!\$B\$21:\$B\$31),1)``
Works great! Thank you

Replies
12
Views
187
Replies
9
Views
82
Replies
3
Views
70
Replies
3
Views
56
Replies
0
Views
196

1,130,169
Messages
5,640,552
Members
417,151
Latest member
ChickenTenderer

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back