Get values from 2 ranges based on certain conditions

[hsandeep]

I have 2 ranges K9:W9 & K16:W16 which generates values including 0 and also “” (blank)
I need 5 formulas:

AA20=Corresponding non zero non blank number from K10:W10 corresponding to the minimum number from K16:W16 else 0

AA21=Non zero non blank number preceding the corresponding non zero non blank number from K10:W10 corresponding to the minimum number from K16:W16 with an additional condition that the minimum number is not in the cell address K16 else 0

AA22=Non zero non blank number preceding the above preceding number from K10:W10 with an additional condition that the minimum number is not in the cell address K16 or L16 else 0

AA23= Non zero non blank number succeeding the corresponding non zero non blank number from K10:W10 corresponding to the minimum number from K16:W16 with an additional condition that the minimum number is not in the cell address W16 else 0

AA24= the minimum number from K16:W16

How to accomplish?
Thanks in advance

10​

22​

30​

4​

55​

66​

100​

111​

2​

99​

10​

20​

33​

1​

2​

3​

4​

40​

50​

-66​

55​

-60​

-50​

-12​

12​

66​

Corresponding

100​

Preceeding

66​

Preceeding 2

55​

Suceeding

111​

Reference

-66​

 

[awoohaw]

Do these formulas work for you?
NOTE: You need to commit to the cell with the CNTL-SHFT-ENTR keystroke (CSE).

Book1
	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
9	10	22	30	4	55	66	100	111	2	99	10	20	33
10
16	1	2	3	4	40	50	-66	55	-60	-50	-12	12	66
17
18
19
20																Corresponding	100	100
21																Preceeding	66	66
22																Preceeding 2	55	55
23																Suceeding	111	111
24																Reference	-66
Sheet1

Cell Formulas
Range	Formula
AB20	AB20	=IFERROR(INDEX($K$9:$W$9,1, SUM((($K$16:$W$16)=MIN($K$16:$W$16))* (COLUMN($K$16:$W$16)-COLUMN($K$16)+1))),0)
AB21	AB21	=IFERROR(INDEX($K$9:$W$9,1, SUM((($K$16:$W$16)=MIN($K$16:$W$16))* (COLUMN($K$16:$W$16)<>COLUMN($K$16))* (COLUMN($K$16:$W$16)-COLUMN($K$16)+1))-1),0)
AB22	AB22	=IFERROR(INDEX($K$9:$W$9,1, SUM((($K$16:$W$16)=MIN($K$16:$W$16))* ( (COLUMN($K$16:$W$16)<>COLUMN($K$16))+ (COLUMN($K$16:$W$16)<>COLUMN($L$16))-1 ) * (COLUMN($K$16:$W$16)-COLUMN($K$16)+1))-2),0)
AB23	AB23	=IFERROR(INDEX($K$9:$W$9,1, SUM((($K$16:$W$16)=MIN($K$16:$W$16))* (COLUMN($K$16:$W$16)<>COLUMN($W$16))* (COLUMN($K$16:$W$16)-COLUMN($K$16)+1))+1),0)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[hsandeep]

=IFERROR(INDEX($K$9:$W$9,1, SUM((($K$16:$W$16)=MIN($K$16:$W$16))* (COLUMN($K$16:$W$16)<>COLUMN($W$16))* (COLUMN($K$16:$W$16)-COLUMN($K$16)+1))+1),0)

awoohaw

Your first 3 formulas work well. But the 4th formula for getting the succeeding number doesn’t work when the minimum number from K16:W16 is in the cell address W16 i.e. the last cell (right most cell) of the range K16:W16. In such case, answer should be 0

 

[Eric W]

How about these non-array formulas?

Book1
	Z	AA
20	Corresponding	100
21	Preceeding	66
22	Preceeding 2	55
23	Suceeding	111
24	Reference	-66
Sheet4

Cell Formulas
Range	Formula
AA20	AA20	=INDEX(K9:W9,MATCH(AA24,K16:W16,0))
AA21	AA21	=IFERROR(INDEX(K9:W9,1/(1/(MATCH(AA24,K16:W16,0)-1))),0)
AA22	AA22	=IFERROR(INDEX(K9:W9,1/(1/(MATCH(AA24,K16:W16,0)-2))),0)
AA23	AA23	=IFERROR(INDEX(K9:W9,MATCH(AA24,K16:W16,0)+1),0)
AA24	AA24	=MIN(K16:W16)

 

[awoohaw]

try these:

Book1
											K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
9											10	22	30	4	55	66	100	111	2	99	10	20	33
16											-70	-72	-71	4	-68	50	-66	55	-60	-50	-12	-67	66
17
18
19
20																										Corresponding		22
21																										Preceeding		10
22																										Preceeding 2		0
23																										Suceeding		30
24																										Reference	-72
Sheet1

Cell Formulas
Range	Formula
AB20	AB20	=IFERROR(INDEX($K$9:$W$9,1, SUM((($K$16:$W$16)=MIN($K$16:$W$16))* (COLUMN($K$16:$W$16)-COLUMN($K$16)+1))),0)
AB21	AB21	=IFERROR(INDEX($K$9:$W$9,1, SUM((($K$16:$W$16)=MIN($K$16:$W$16))* (COLUMN($K$6:$W$16)>COLUMN($K$16))* (COLUMN($K$16:$W$16)-COLUMN($K$16)+1))-1),0)
AB22	AB22	=IFERROR(INDEX($K$9:$W$9,1, SUM((($K$16:$W$16)=MIN($K$16:$W$16))* (COLUMN($K$6:$W$16)>COLUMN($L$16))* (COLUMN($K$16:$W$16)-COLUMN($K$16)+1))-2),0)
AB23	AB23	=IFERROR(INDEX($K$9:$W$9,1, SUM((($K$16:$W$16)=MIN($K$16:$W$16))* (COLUMN($K$16:$W$16)-COLUMN($K$16)+1))+1),0)
AA24	AA24	=MIN(K16:W16)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[hsandeep]

try these:

Thanks a lot awoohaw for your time, efforts & help. It works. Thank you.

 

[awoohaw]

You're welcome. I'm sure there are more concise formulas that do the same thing. But, this works, so great.

Best Wishes.