Finding closest value to the left or right, based on index match, which returned a blank value.

[Chriskkw]

Hi guys, i,m new and i have a very interesting one for you. i did the usual index match and it returned a blank value. Based on that blank value. it would return the closest number based on number of blank cells.

here's an extract. been through many searches cant seem to find a solution.

So as you can see. the value returned is the cell to the right of 16.17, and i need it to return 16.17 but it need i to be dynamic. as you can see, the next blanks are on top, and i need it to return 340. i have like 500 rows with about 120 columns of data so i cant use a static formula.

Anyone has any idea how to?

thanks for the replies in advanced!

 

[jasonb75]

So are we looking for rows that only contain 1 number, lowest value first?

Book1
	A	B	C	D	E	F	G
2			0.691805
3	0.245082	0.841826	0.133911	0.760946	0.471026	0.5839	0.941654
4	0.564773	0.226418	0.7683	0.404371	0.016822	0.685966	0.936436
5	0.143386	0.603925	0.747046	0.051866	0.013991	0.055107	0.407227
6				0.619415
7
8		0.619415
9		0.691805
10
Sheet2

Cell Formulas
Range	Formula
B8:B10	B8	=IFERROR(AGGREGATE(15,6,$A$2:$G$6/(SUBTOTAL(2,OFFSET($A$2,ROW($A$2:$A$6)-ROW($A$2),0,1,COLUMNS($A$2:$G$2)))=1),ROWS(B$8:B8)+COUNTIF($A$2:$G$6,"")),"")

 

[Chriskkw]

Hi Jason, thanks so much for your reply!! However, what i did was a index match before that, i was hoping to find the value based on the Blank text.

so if i'm looking for example 28/5/2020, Arthur, for the first cell reference, i would want a return value of either 71 or 75. any ideas?

i figured how to paste excel formulas, hope it works.

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
2	Date	28/5/2020		Value
3	Name	Arthur		0
4				0
5				15.05
6				#NUM!
7
8		Name		20/5/2020	21/5/2020	22/5/2020	25/5/2020	26/5/2020	27/5/2020	28/5/2020	29/5/2020	1/6/2020	2/6/2020	3/6/2020	4/6/2020
9		Arthur		0	0	71	0	0	0	0	75	0	0	0	0
10		Arthur		0	0	0	0	0	0	0	210	0	0	0	0
11		Cayden		456	452	454	456	456	460	460	464	456	458	460	468
12		Cayden		0	0	3.104	0	0	0	0	0	0	0	0	0
13		Barry		36.882	38.462	35.634	0	37.502	34.954	34.544	35.836	39.564	41.97	43.32	44.06
14		Barry		17.09	17.41	17.29	0	16.88	17.11	17.36	18.23	17.79	17.94	18.18	17.65
15		Cayden		22.62	22.84	22.57	0	22.12	22.12	22.38	23.08	23	23.15	22.96	22.97
16		Dennis		0	0	14540	0	0	0	0	14456	0	0	0	0
17		Dennis		391.6	389	396.6	0	388.6	385.2	392.6	392	391.4	379.6	382.6	379
18		Cayden		13.62	13.6	13.7	0	13.56	13.36	13.41	13.39	13.35	13.32	13.52	13.9
19		Arthur		15.25	15.25	15.35	0	15.25	15.05	15.05	15.1	15.1	15.1	15.3	15.7
20		Barry		5.62	5.772	5.642	5.782	5.77	5.616	5.846	5.756	6.638	6.794	6.764	6.932
21		Dennis		58.075	60.975	57.975	0	62.375	61.475	58.675	59.425	63.925	67.35	68.75	67.9
Sheet1

Cell Formulas
Range	Formula
E3:E6	E3	=INDEX($E$9:$P$21,AGGREGATE(15,3,($C$9:$C$21=$C$3)/($C$9:$C$21=$C$3)*ROW($C$9:$C$21)-ROW($C$8),ROWS($B$9:B9)),MATCH($C$2,$E$8:$P$8))

 

[jasonb75]

find the value based on the Blank text

Your example above doesn't have blanks, so should it be based on blank or zero?

Blank (with a formula), empty and zero often need to be dealt with in different ways, especially if there can be genuine zero values in the data mixed with blank / empty cells.

 

[Chriskkw]

Ah I'm sorry, I'm still getting used to the terms as an amateur, I didn't know it is treated differently.

It is blanks.

I took the blanks and divided it by a constant hence it showed 0. Sorry about that. Thanks for getting back so quick. Really appreciate it!

 

[jasonb75]

so if i'm looking for example 28/5/2020, Arthur, for the first cell reference, i would want a return value of either 71 or 75. any ideas?

Looking at your example again, could you explain why 71 or 75 and not 210 (in L10)?

I'm assuming that row 19 is excluded because the column for the date in C2 is not blank?

First attempt, based on a few assumptions.

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
2	Date	28/05/2020		Value
3	Name	Arthur		71
4				75
5
6
7
8		Name		20/05/2020	21/05/2020	22/05/2020	25/05/2020	26/05/2020	27/05/2020	28/05/2020	29/05/2020	01/06/2020	02/06/2020	03/06/2020	04/06/2020
9		Arthur				71					75
10		Arthur									210
11		Cayden		456	452	454	456	456	460	460	464	456	458	460	468
12		Cayden				3.104
13		Barry		36.882	38.462	35.634		37.502	34.954	34.544	35.836	39.564	41.97	43.32	44.06
14		Barry		17.09	17.41	17.29		16.88	17.11	17.36	18.23	17.79	17.94	18.18	17.65
15		Cayden		22.62	22.84	22.57		22.12	22.12	22.38	23.08	23	23.15	22.96	22.97
16		Dennis				14540					14456
17		Dennis		391.6	389	396.6		388.6	385.2	392.6	392	391.4	379.6	382.6	379
18		Cayden		13.62	13.6	13.7		13.56	13.36	13.41	13.39	13.35	13.32	13.52	13.9
19		Arthur		15.25	15.25	15.35		15.25	15.05	15.05	15.1	15.1	15.1	15.3	15.7
20		Barry		5.62	5.772	5.642	5.782	5.77	5.616	5.846	5.756	6.638	6.794	6.764	6.932
21		Dennis		58.075	60.975	57.975		62.375	61.475	58.675	59.425	63.925	67.35	68.75	67.9
Sheet3

Cell Formulas
Range	Formula
E3	E3	=LOOKUP(1E+100,INDEX($E$9:INDEX($E$9:$P$21,0,MATCH($C$2,$E$8:$P$8,0)-1),MATCH($C$3,$C$9:$C$21,0),0))
E4	E4	=LOOKUP(1E+100,INDEX(INDEX($E$9:$P$21,0,MATCH($C$2,$E$8:$P$8,0)+1):$P$21,MATCH($C$3,$C$9:$C$21,0),0))

 

[Chriskkw]

Hi Jason, yes you are right, so basically my formula throws out all values for "Arthur". but 71 or 75 was referring to row 1, whereas if i scroll down, it will refer to row 2, which choosing the nearest value would be 210.

So example if i choose 22/5/2020, Cayden for my helper cells. it would return 4 results.

what i'm trying to achieve is that if i choose "Cayden" "21/5/2020", it would throw out "452", "3.104", "22.84", "13.6", in which "3.104" would be the nearest value when it's blank.

Test 1.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
2	Date	22/5/2020		Value
3	Name	Cayden		454
4				3.104
5				22.57
6				13.7
7
8		Name		20/5/2020	21/5/2020	22/5/2020	25/5/2020	26/5/2020	27/5/2020	28/5/2020	29/5/2020	1/6/2020	2/6/2020	3/6/2020	4/6/2020
9		Arthur				71					75
10		Arthur									210
11		Cayden		456	452	454	456	456	460	460	464	456	458	460	468
12		Cayden				3.104
13		Barry		36.882	38.462	35.634		37.502	34.954	34.544	35.836	39.564	41.97	43.32	44.06
14		Barry		17.09	17.41	17.29		16.88	17.11	17.36	18.23	17.79	17.94	18.18	17.65
15		Cayden		22.62	22.84	22.57		22.12	22.12	22.38	23.08	23	23.15	22.96	22.97
16		Dennis				14540					14456
17		Dennis		391.6	389	396.6		388.6	385.2	392.6	392	391.4	379.6	382.6	379
18		Cayden		13.62	13.6	13.7		13.56	13.36	13.41	13.39	13.35	13.32	13.52	13.9
19		Arthur		15.25	15.25	15.35		15.25	15.05	15.05	15.1	15.1	15.1	15.3	15.7
20		Barry		5.62	5.772	5.642	5.782	5.77	5.616	5.846	5.756	6.638	6.794	6.764	6.932
21		Dennis		58.075	60.975	57.975		62.375	61.475	58.675	59.425	63.925	67.35	68.75	67.9
Sheet1

Cell Formulas
Range	Formula
E3	E3	=(INDEX($E$9:$P$21,AGGREGATE(15,3,($C$9:$C$21=$C$3)/($C$9:$C$21=$C$3)*ROW($C$9:$C$21)-ROW($C$8),ROWS($B$9:B9)),MATCH($C$2,$E$8:$P$8)))
E4:E6	E4	=INDEX($E$9:$P$21,AGGREGATE(15,3,($C$9:$C$21=$C$3)/($C$9:$C$21=$C$3)*ROW($C$9:$C$21)-ROW($C$8),ROWS($B$9:B10)),MATCH($C$2,$E$8:$P$8))

 

[jasonb75]

whereas if i scroll down, it will refer to row 2, which choosing the nearest value would be 210.

You can't base a formula on scroll position, it needs an identifier of some kind.