finding a value with parameters

[soonic]

Hi

Is it possible to make it work in excel in this situation:

I've got 3 columns: A,B,C. I want to find the smallest number in col C, but
including rows with the same value in col B. The answer would be from col A
with the same row as found value in C.

Example:
A B C
A1 J 8
A2 K 4
A3 J 6
A4 P 1

I'm looking for lowest number in col C but including only rows w "J" in col
B. So, the answer should be "A3" in col A.

Any ideas how to make it work?

 

[Jarek Kujawa]

=MIN(IF($A$2:$A$5="j",$B$2:$B$5))

array-enter this formula i.e. with CTRL+SHIFT+ENTER instead of using
ENTER only

 

[soonic]

=MIN(IF($A$2:$A$5="j",$B$2:$B$5))
array-enter this formula i.e. with CTRL+SHIFT+ENTER instead of using
ENTER only

thank you, but this is the first part. In final answer should be a value
from col A as the same row of found number in C.
It doesn't work with my example.
Col A have values A1, A2, A3, A4. The answer should be "A3".

 

[Scossa]

Hi

Is it possible to make it work in excel in this situation:

I've got 3 columns: A,B,C. I want to find the smallest number in col C, but
including rows with the same value in col B. The answer would be from colA
with the same row as found value in C.

Example:
A    B    C
A1  J     8
A2  K    4
A3  J     6
A4  P     1

I'm looking for lowest number in col C but including only rows w "J" in col
B. So, the answer should be "A3" in col A.

Try:

=INDEX($A$2:$A$5,MATCH(MIN(IF(($B$2:$B$5)="J",($C$2:$C$5))),($C$2:$C$5)
*(($B$2:$B$5)="J"),0))

N.B.: Matrix formula (confirm with CTRL+SHIFT+ENTER)

Bye!
Scossa

 

[Bernard Liengme]

I will assume your data goes down to row 20
In F1 I have typed the letter J - the letter you are looking for
I will show you how I developed the formula to get A3

In F2 I used =MIN(IF(B1:B20=F1,C1:C20)) and entered this as an array formula
using CTRL+SHIFT+ENTER (not just Enter) This correctly returned the value 6

Now I want to know where this occurred. I used this array formula
=MATCH(MIN(IF(B1:B20=F1,C1:C20)),C1:C20,0)
which returned 3 since the 6 is in the third item in C1:C20

Finally I want to know what is in column A in this position, this I can find
with array formula
=INDEX(A1:A20,MATCH(MIN(IF(B1:B20=F1,C1:C20)),C1:C20,0))
This returned A3 as we hoped it would

best wishes

 

[Scossa]

with array formula
=INDEX(A1:A20,MATCH(MIN(IF(B1:B20=F1,C1:C20)),C1:C20,0))
This returned A3 as we hoped it would

in this examples (with the min (6) also for K):

A1 J 8
A2 K 6
A3 J 6
A4 P 5

don't works.

My solution:

=INDEX($A$2:$A$5,MATCH(MIN(IF(($B$2:$B$5)="J",($C$2:$C$5))),($C$2:$C$5)
*(($B$2:$B$5)="J"),0))

works fine becouse target are "fltered" for "J".

Bye!
Scossa

 

[soonic]

My solution:

=INDEX($A$2:$A$5,MATCH(MIN(IF(($B$2:$B$5)="J",($C$2:$C$5))),($C$2:$C$5)
*(($B$2:$B$5)="J"),0))
works fine becouse target are "fltered" for "J".

this is good point! thank you for help Scossa it works great now. Thank you
also Bernard for explanation.
I've got another problem in my real excel data. I'm using outline in every
row and this array formula is being used inside it, so pressing ctrl,shift
and enter gives me an error.

Going back to my example add a row between rows C1 nad C5 and try to put
this array formula in C3. You will get an error after pressing
ctrl+shift+enter. How to exclude the whole row which contains this array
formula from its range?

 

[Scossa]

this is good point! thank you for help Scossa it works great now. Thank you
also Bernard for explanation.

Thank you for backfeed.

I've got another problem in my real excel data. I'm using outline in every
row and this array formula is being used inside it, so pressing ctrl,shift
and enter gives me an error.

Going back to my example add a row between rows C1 nad C5 and try to put
this array formula in C3. You will get an error after pressing
ctrl+shift+enter. How to exclude the whole row which contains this array
formula from its range?

Try this:
fill outline cells with a "dummy" value (p.e.: "Z1" in A, "Z" in B, 0
in C) foreground color same as backgroundcolor.

Apologize for my bad english.

Bye!
Scossa

 

[Scossa]

Thank you for backfeed.

opps... feedback

sorry.

Bye!
Scossa

 

[soonic]

Try this:
fill outline cells with a "dummy" value (p.e.: "Z1" in A, "Z" in B, 0
in C) foreground color same as backgroundcolor.

I'm sorry but I don't get it now. This array formula is in a cell of C
column like:

A B C
a1 K 6
a2 J 8
x xv array forumula
a4 J 6

when accepting it by pressing ctrl+shift+enter I get an error (or
information something with "recall serial inside formula") and then it shows
0. The best it would be to exclude the outline from the range of array
formula, I don't now how to do it.(the easiest way is to change the location
of the written formula but it would mess up in my spreadsheet)

 

[Scossa]

0. The best it would be to exclude the outline from the range of array
formula, I don't now how to do it.(the easiest way is to change the location
of the written formula but it would mess up in my spreadsheet)

Sorry, no solution in mind (without VBA).

Bye!
Scossa

 

[soonic]

My solution:
=INDEX($A$2:$A$5,MATCH(MIN(IF(($B$2:$B$5)="J",($C$2:$C$5))),($C$2:$C$5)
*(($B$2:$B$5)="J"),0))

can you modify this formula so it would show all the same matches? Like in
the example below

A B C
a1 n 4
a2 J 6
a3 J 7
a4 J 6
.....

the answer would be a2 and a4. Is it possible to do that without VB?