Smallest number through INDEX MATCH

**anggiemelisa** · 08-23-2019, 08:50 PM

Hi everyone,

I'm struggling with my data that does not give the result I want, could you please help me?

Capture.PNG

How to get 10 (on column 'unit' or column B) for item code 1172 (as 10, is the smallest total unit for 1172)?
How to get -20 (on column 'unit' or column B) for item code 1856 (as -20, is the smallest total unit for 1856)?

I've tried =MIN(B2:B9,INDEX(B2:B9,MATCH(C2,A2:A9,0))) but the result is -20
I've tried =SMALL(INDEX(B2:B9,MATCH(C2,A2:A9,0),0),ROWS(1:1)) but the result is 12

Thank you so much for your help, really appreciate it.

-Melissa-

**Sam Capricci** · 08-23-2019, 09:19 PM

try this ARRAY formula =MIN(IF(A2:A9=D2,B2:B9))
activated by ctrl+shift+enter, you'll know it is active when the {} appear at each end of the formula. Can't add them yourself.

**Phuocam** · 08-23-2019, 11:19 PM

Or try:

=AGGREGATE(15,6,$B$2:$B$9/($A$2:$A$9=D2),1)

**MarvinP** · 08-24-2019, 01:06 AM

Hi Melissa and welcome to the forum,

This looks like a MinIfs() problem to me. See the attached where I have the problem and answer done for you.

MinIfs for Melissa.xlsx

**AliGW** · 08-24-2019, 01:24 AM

MINIFS is not available in Excel 2010.

**anggiemelisa** · 08-24-2019, 02:55 AM

You solve my 2 hours problem in just a second :,) Thanks

**Pepe Le Mokko** · 08-24-2019, 02:58 AM

Originally Posted by anggiemelisa

You solve my 2 hours problem in just a second :,) Thanks

Perhaps you could add which solution you are referring to ?

**AliGW** · 08-24-2019, 02:58 AM

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

**anggiemelisa** · 08-24-2019, 03:01 AM

Thank you everyone for the help! All formula work! Can't believe this has so many ways to get the result I want

**AliGW** · 08-24-2019, 11:13 AM

Thanks for the rep. As I said in my thank you message to you earlier, it would be helpful to upgrade your profile to show that you are using two versions of Excel and which they are.

Smallest number through INDEX MATCH

LinkBack

Thread Tools

Rate This Thread

Display

Smallest number through INDEX MATCH

Re: Smallest number through INDEX MATCH

Re: Smallest number through INDEX MATCH

Re: Smallest number through INDEX MATCH

Re: Smallest number through INDEX MATCH

Re: Smallest number through INDEX MATCH

Re: Smallest number through INDEX MATCH

Re: Smallest number through INDEX MATCH

Re: Smallest number through INDEX MATCH

Re: Smallest number through INDEX MATCH

Thread Information

Users Browsing this Thread

Similar Threads

sort data (smallest to largest) with formula (index - match)

Index Match to find smallest 6 numbers problem

[SOLVED] Converting Hlookup to Index Match with variable column index number

[SOLVED] Index Match Smallest return

[SOLVED] Counting the number of times INDEX/MATCH finds more than 1 match.

[SOLVED] INDEX MATCH and MIN/SMALL to pull first and second smallest values from array

INDEX MATCH 3rd SMALLEST NUMBER

Bookmarks

Bookmarks

Posting Permissions