Get value from a range based on certain conditions

[hsandeep]

I want to get value from B2:E6 corresponding to A2:A6 which Matches with A10 and also corresponding to the cell value in the column G21:J21 whose value is MINIMUM and NEGATIVE else 0

How to accomplish?
Thanks in advance

10​	111​	222​	333​	444​
20​	100​	200​	300​	400​
30​	1000​	2000​	3000​	4000​
40​	10000​	20000​	30000​	40000​
50​	11​	22​	33​	44​
30​
10​
20​
30​
40​
50​
						1111​	-22​	-11​	555​

 

[PeteWright]

Could you post some more details? I still don't know what exactly you are looking for.

Book1.xlsm
	A	B	C	D	E	F	G	H	I	J
1
2	10	111	222	333	444
3	20	100	200	300	400
4	30	1000	2000	3000	4000
5	40	10000	20000	30000	40000
6	50	11	22	33	44
7
8
9
10	30
11
12
13
14
15	10
16	20
17	30
18	40
19	50
20
21							1111	-22	-11	55
Sheet3

So that's your data.
I don't understand the meaning of row 21 (G21:J21)

 

[Kaper]

Try in B10 (or other cell where you need it):

=INDEX(B2:E6,MATCH(A10,A2:A6,0),MATCH(MIN(H21:K21),H21:K21,0))

Whoops that was not including this "negative else 0" part.

So, if I understand it right:

=if(MIN(H21:K21)<0,INDEX(B2:E6,MATCH(A10,A2:A6,0),MATCH(MIN(H21:K21),H21:K21,0)),0)

 

[hsandeep]

Try in B10 (or other cell where you need it):

=INDEX(B2:E6,MATCH(A10,A2:A6,0),MATCH(MIN(H21:K21),H21:K21,0))

Whoops that was not including this "negative else 0" part.

So, if I understand it right:

=if(MIN(H21:K21)<0,INDEX(B2:E6,MATCH(A10,A2:A6,0),MATCH(MIN(H21:K21),H21:K21,0)),0)

Kaper You understood it right. This is what I needed. Thanks Kaper for your efforts, time and help

 

[Kaper]

Glad to hear it did the job. And thanks for marking my post as a solution.