Finding smallest number in a column with the minumum value in another cell along the row

[Churt]

Hi,

So here's what I've been struggling with for some days now. I have the following sample data:

A	B
18	001
0	002
7	003
0	010
35	011
17	012
7	013
1	020
5	021
8	022
6	023

I need the smallest number in column A and call out the respective column B value. But if there are two numbers that are the smallest (in this case two 0's in column A) then I need to call out whichever column B that has the minimum sumproduct of column B (that is; 0+0+1=1, 0+0+2=2, ... , 0+1+0=1,...).

Therefore, answer should be: 010

How can i do this and get back the proper value from column B???

Much thanks in advance.

-C

[NBVC]

Assuming data in A1:B11,

Get min, in say E2, with:

=MIN(A1:A11)

and get min match with:

=INDEX(B1:B11,MATCH(MIN(IF(A1:A11=E2,LEFT(B1:B11)+MID(B1:B11,2,1)+RIGHT(B1:B11))),IF(A1:A11=E2,LEFT(B1:B11)+MID(B1:B11,2,1)+RIGHT(B1:B11)),0))

confirmed with CTRL+SHIFT+ENTER not just ENTER

[dilipandey]

Hi Churt,

Welcome to the forum.

Try using below array formula:-

{=INDEX($A$1:$C$11,SMALL(IF(($A$1:$A$11=MIN($A$1:$A$11))*($C$1:$C$11=MIN($C$1:$C$11)),ROW($C$1:$C$11),""),1),2)}

see attached:- smallest number in a col with the min value in another col.xlsx


Regards,
DILIPandey

<click on below 'star' if this helps>

[Ace_XL]

I am afraid, Dilipandey's formula does not work in case the sum of the digits against the minimum value in Col A is not the minimum amongst all values in Column B.

Using NBVC's formula, you probably dont need Cell E2 as a helper cell. Simply use

=INDEX(B1:B11,MATCH(MIN(IF(A1:A11=MIN(A1:A11),LEFT(B1:B11)+MID(B1:B11,2,1)+RIGHT(B1:B11))),IF(A1:A11=MIN(A1:A11),LEFT( B1:B11)+MID(B1:B11,2,1)+RIGHT(B1:B11)),0))

confirmed with CTRL+SHIFT+ENTER not just ENTER

[Churt]

It does not seem to be working for me. When I past in the last formula, excel isn't getting past "IF(A1:A11=E2," and I don't know why. I've actually been having issues applying other formulas with similar logic, for whatever reason excel doesn't see it as valid.

When I hit "Show Calculation Steps...", it shows:

=INDEX(B1:B11,MATCH(MIN(IF(FALSE,LEFT(B1:B11)+MID(B1:B11,2,1)+RIGHT(B1:B11))),IF(A1:A11=E2,LEFT(B1:B11)+MID(B1:B11,2,1)+RIGHT(B1:B11)),0))

---------- Post added at 12:15 PM ---------- Previous post was at 12:06 PM ----------

Hi Churt,

Welcome to the forum.

Try using below array formula:-

{=INDEX($A$1:$C$11,SMALL(IF(($A$1:$A$11=MIN($A$1:$A$11))*($C$1:$C$11=MIN($C$1:$C$11)),ROW($C$1:$C$11),""),1),2)}

see attached:- Attachment 173153


Regards,
DILIPandey

<click on below 'star' if this helps>

Hi

I opened the excel file and it looked like it worked. But then I clicked in the formula bar to evaluate the formula and when I hit enter, without making any changes, I got "#VALUE!" How does that even happen when I didn't change anything?? I clicked undo and all was well, did the same thing and it didn't work anymore. Not sure what's going on.

When I hit "Show Calculation Steps...", it shows:

=INDEX($A$1:$C$11,SMALL("",1),2)

Apparently the IF statement was FALSE?

Could it be that the version I have hasn't gotten any updates form Microsoft or something? And that's why excel can't seem to evaluate logic that includes an array and a cell?

[NBVC]

Here is mine in a workbook....

remember you need to confirm by holding the CTRL and SHIFT keys down, then press ENTER. You will see { } brackets appear.

[Churt]

I am afraid, Dilipandey's formula does not work in case the sum of the digits against the minimum value in Col A is not the minimum amongst all values in Column B.

Using NBVC's formula, you probably dont need Cell E2 as a helper cell. Simply use

=INDEX(B1:B11,MATCH(MIN(IF(A1:A11=MIN(A1:A11),LEFT(B1:B11)+MID(B1:B11,2,1)+RIGHT(B1:B11))),IF(A1:A11=MIN(A1:A11),LEFT( B1:B11)+MID(B1:B11,2,1)+RIGHT(B1:B11)),0))

confirmed with CTRL+SHIFT+ENTER not just ENTER

Hi,

This isn't working for me either. I'm having the same issue I described in my initial reply.
My I need to check that I am getting updates from Microsoft office?
Any thoughts on that?


Again thank you to all that are trying to help, it's very much appreciated. hopefully we can figure out what's going on.

[NBVC]

Have you looked at my attachment? Did you confirm with CSE key combo?

Maybe you can post a sample attachment showing how your data is seen.

[Churt]

Here is mine in a workbook....

remember you need to confirm by holding the CTRL and SHIFT keys down, then press ENTER. You will see { } brackets appear.

Hahahah wow you're right! I didn't do that when I pasted the formula. I feel like a noob haha...
What exactly does holding the CTRL and SHIFT keys down, then pressing ENTER do anyway?
Also, will this work if I have say 3 or 4 or more of the same min values in the same column?

Thanks for the clarification!!!

[NBVC]

It's an Array Formula and that is how you tell Excel.

It should work for multiple matches in column A

[NBVC]

Please also mark your thread as SOLVED if it is so...

[Churt]

It's an Array Formula and that is how you tell Excel.

It should work for multiple matches in column A

Awsome, thank you for all the help and clarification! You solved this like a boss!
I just got through applying it to my application and finished verifying that worked. I'll mark this as solved shortly.