Nested If formula (little tricky)

ChargerRT

Board Regular
Joined
Jul 24, 2016
Messages
68
Hi guys,

I have this table

Table.jpg

I need a formula that designates names “A” and “B” in Com AA based on the following criteria:

  1. The highest value in col A should be named “A”
  2. The highest value in Col T AND lowest value in Col K should be named “B” (if it is type API in Col E)

Of course if there is only on API type in Col E, then there won’t be any “B” in col AA
There always should be A and B in Col AA as long as there are at least 2 APIs in col E
In other words, in the table shown in the above image, AA2 is “A” and AA2 is “B”
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
I also should mention that for "B", highest value in Col T takes precedence over the value in Col K
 
Upvote 0
Sorry, not allowed to access file hosting sites, can you post your sample here?
 
Upvote 0
C
E
K
T
Z
AA
1
Product name
Type
Smallest batch size
Max Daily Dose
Score
Designation
2
Atrosol
API
700
2
54
3
Lidocaine hcl 1 %
API
1000
300
16
4
0.9 % Sodium chloride
Electro
3500
4581
5
5
Water For Injections
N/A
3500
N/A
2
6
Water for Reconstitution
N/A
1750
N/A
1
7
Ringer Lactate
API
3500
250
5

<tbody>
</tbody>

This is how the table looks like. I didn't put the in between columns because they are irrelevant.
 
Last edited:
Upvote 0
Basically, the formula should find the highest score in Z and names "A" then it should find "B" but "B" has the following criteria:
1) It should be API type
2) It should be It should be the highest value in Col T and then it should find the lowest value in Col K (the lowest of K value found for the highest of T)

In my table
Row AA2 will be designated "A" - because it has the highest value in col Z
Row AA3 will be designated "B"
 
Upvote 0

Forum statistics

Threads
1,203,534
Messages
6,055,960
Members
444,839
Latest member
laurajames

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
Back
Top