Formula to search biggest number in range

DarkSmile

New Member
Joined
Feb 22, 2021
Messages
22
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
Excel Formula:
=AGGREGATE(14,6,(SEARCH('ISO22000'!$A$21:$A$31,B4)>0)*('ISO22000'!$B$21:$B$31),1)
 
Solution

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top