Min and Index Match or Vlookup with multiple criteria

nyconfidential

New Member
Joined
Jul 22, 2015
Messages
46
Office Version
  1. 365
  2. 2016
Hi all - I am trying to do either a vlookup or an index match that will find the min value from one column that has a matching value in another column. See below - my goal is to take ALL of the values in Row 2, and search column F for the rows that match, and then return the min value of Column G. So, in this case, I'd want to return the value of "43", since the min Qty of the values listed in Row 2 is 43(RAM-B-238 from Row 2). Can anyone tell me the best way to accomplish this? thanks!

component1.PNG
 

Attachments

  • component.PNG
    component.PNG
    22.8 KB · Views: 0

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,153
Office Version
  1. 365
Platform
  1. Windows
What version of Excel are you using?
Please update your account details to show this, as it affects which functions you can use.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,153
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
+Fluff v2.xlsm
ABCDEFG
1
2abcdefhijxyz27
3122519abc21
4mno17
5hij30
6def25
7abc12
8def29
9xyz28
10hij19
11abc30
Master
Cell Formulas
RangeFormula
A3:C3A3=AGGREGATE(15,6,$G$2:$G$11/($F$2:$F$11=A2),1)
 

nyconfidential

New Member
Joined
Jul 22, 2015
Messages
46
Office Version
  1. 365
  2. 2016
Sorry Fluff, its 2016, will update my account shortly
Ok, how about
+Fluff v2.xlsm
ABCDEFG
1
2abcdefhijxyz27
3122519abc21
4mno17
5hij30
6def25
7abc12
8def29
9xyz28
10hij19
11abc30
Master
Cell Formulas
RangeFormula
A3:C3A3=AGGREGATE(15,6,$G$2:$G$11/($F$2:$F$11=A2),1)
Thanks Fluff - I notice that you are searching for one value at a time (explicitly specifying A2 in the formula) - is there a way i can do this by searching for all of the values in row 2 at once (abc, def, hij) and return the min value from row G in one formula?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,153
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
Excel Formula:
=AGGREGATE(15,6,$G$2:$G$11/(($F$2:$F$11=A2)+($F$2:$F$11=B2)+($F$2:$F$11=C2)),1)
You have shown that you also have Excel 365, do you have the Filter function?
 

Watch MrExcel Video

Forum statistics

Threads
1,114,083
Messages
5,545,857
Members
410,711
Latest member
Josh324
Top