Lookup formula to find first non-blank value in a dynamic range

[chrisjwhite90]

I need help for a lookup formula to find non-blank values in a dynamic range.

Here’s a sample of the spreadsheet below. The # of rows between each variable in column A can vary, so I’m having trouble devising a way to find these non-blank values.

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Store1	Yes	Yes	Yes	Yes	1453.322	3
2								Apples	22
3								Oranges	9
4								Bananas	5
5												84.00%	42	50	84.0% at 2017-09-11 17:03:03 -0400
6																5	5	100%
7	Store2	Yes	Yes	Yes	Yes	1453.322	3
8								Apples	22
9								Oranges	9
10								Bananas	5
11												80.00%	40	50	80.0% at 2017-09-11 17:03:03 -0400
12																0	0	100%
13	Store3	Yes	Yes	Yes	Yes	1453.322	3
14								Apples	22
15								Oranges	9
16								Bananas	5
17								Peaches	3
18												88.00%	44	50	88.0% at 2017-09-11 17:03:03 -0400
19																3	3	100%

<tbody>
</tbody>

For example: Find “Store2”’s row in column A, and return the first non-blank cell in column L below this row (it looks below L7 to return the first non-blank cell). The formula should find cell L11 and return 80%.

Likewise, the same formula in a different cell should find “Store3”’s row in column A, and return the first non-blank cell in column L below this row. The formula should find cell L18 and return 88%.

Any help would be appreciated, thanks.

 

[Aladin Akyurek]

Let A1:R19 of Sheet1 house the data you posted.

let A2:A3 of Sheet2 house Store2 and Store3.

In B2 of Sheet2 control+shift+enter, not just enter, and copy down:

=INDEX(Sheet1!$L$1:$L$19,MIN(IF(ISNUMBER(Sheet1!$L$1:$L$19),IF(ROW(Sheet1!$L$1:$L$19)-ROW(Sheet1!$L$1)+1>MATCH(A2,Sheet1!$A$1:$A$19,0),ROW(Sheet1!$L$1:$L$19)-ROW(Sheet1!$L$1)+1))))

 

[chrisjwhite90]

This worked perfectly. Thank you!!!

 

[Aladin Akyurek]

This worked perfectly. Thank you!!!

You are welcome.