Get values from a range based on certain conditions

[hsandeep]

I want to get the value from contiguous columns B2:M2 corresponding to the MINIMUM non zero and non-blank value from C6:M6 else 0.

Also, I need the preceding cell’s value of the above corresponding cell from B2:M2 else 0

I need 2 formulas

Thanks in advance

1​	2​	3​	55​	234​	67​	55​	8​	9​	10​	30​	20​
	10​	20​	44​	-13​	60​	-18​	66​	0​	100​	300​	-29​
								67​	Predceding
								55​	Corresponding to minimum C6:M6

 

[Peter_SSs]

Why do you show 67 and 55 as the results? The minimum non zero and non-blank value from C6:M6 is -29 isn't it?

Wouldn't it be this?

24 02 17.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M
1
2	1	2	3	55	234	67	55	8	9	10	30	20
3
4
5
6		10	20	44	-13	60	-18	66	0	100	300	-29
7
8
9									30	Predceding
10									20	Corresponding to minimum C6:M6
Sheet4

Cell Formulas
Range	Formula
J9	J9	=INDEX(B2:M2,MATCH(MIN(C6:M6),C6:M6,0))
J10	J10	=INDEX(C2:M2,MATCH(MIN(C6:M6),C6:M6,0))

 

[Peter_SSs]

Sorry, I forgot to eliminate blank/zero values as possibilities. You may want this instead.

24 02 17.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M
1
2	1	2	3	55	234	67	55	8	9	10	30	20
3
4
5
6		10	3	44		60		66	0	100	300
7
8
9									2	Predceding
10									3	Corresponding to minimum C6:M6
Sheet5

Cell Formulas
Range	Formula
J9	J9	=INDEX(B2:M2,MATCH(AGGREGATE(15,6,C6:M6/(C6:M6<>0),1),C6:M6,0))
J10	J10	=INDEX(C2:M2,MATCH(AGGREGATE(15,6,C6:M6/(C6:M6<>0),1),C6:M6,0))

 

[hsandeep]

Why do you show 67 and 55 as the results? The minimum non zero and non-blank value from C6:M6 is -29 isn't it?

Typo Sir, it is 29 instead of -29.
Both formulas should also be checking C6:M6 for non zero and non blank to get the 2 answers

 

[hsandeep]

Yes I needed this. Thanks Sir.
1 more: Also, I need the succeeding cell’s value of the above corresponding cell from B2:M2 else 0 (answer is 8 here)

 

[Peter_SSs]

, I need the succeeding cell’s value of the above corresponding cell from B2:M2 else 0

Try

=IFERROR(INDEX(C2:M2,MATCH(AGGREGATE(15,6,C6:M6/(C6:M6<>0),1),C6:M6,0)+1),0)