Find the nearest non blank cell in a row

[BMPeers]

I have a problem which I have been trying to solve but I need help.
Thanks in advance for any answers.

I have time series data, much of which is in complete. So for example there might be 4 years missing in the series. What I'd like is a formula which finds the nearest non-blank value both forward or backwards in time.

So for my example below, I'd like cells G2 and H2 to equal 21, and I2 and J2 to equal 51.

The formulas will be entered from row 9 downwards as a complete matrix.

Note my real dataset is many thousands of rows and more years...

 

[DRSteele]

I only know how to fill in the blank cells in each row with the last number that appeared in a non-blank cell. In your data, G2 through J2 would contain 21. I'm not sure how you'd discriminate between 'going forward' and 'going backwards.' Perhaps someone more clever can noodle it out.

Excel 2012

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		1965	1966	1967	1968	1969	1970	1971	1972	1973	1974	1975	1976	1977	1978	1979	1980
2	dog	42	78	66	91	74					65	29	14	42	17	88	50
3	cat	47	51	52	96	21	30	21	84	31	40	44	83	61	57	35	2
4	sheep	14	82	37	9	91	87							22	98	29	74
5	chicken	59	72	98	74	46	41							31	59	26	12
6	fox	56	84	41	70	64	14	39	56	97	39	23	54	9	6	54	96
7
8	dog	42	78	66	91	74	74	74	74	74	65	29	14	42	17	88	50
9	cat	47	51	52	96	21	30	21	84	31	40	44	83	61	57	35	2
10	sheep	14	82	37	9	91	87	87	87	87	87	87	87	22	98	29	74
11	chicken	59	72	98	74	46	41	41	41	41	41	41	41	31	59	26	12
12	fox	56	84	41	70	64	14	39	56	97	39	23	54	9	6	54	96

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet31​

Worksheet Formulas Cell Formula B8 =LOOKUP(2,1/(NOT(ISBLANK($B2:B2))),$B2:B2) C8 =LOOKUP(2,1/(NOT(ISBLANK($B2:C2))),$B2:C2) D8 =LOOKUP(2,1/(NOT(ISBLANK($B2:D2))),$B2:D2) E8 =LOOKUP(2,1/(NOT(ISBLANK($B2:E2))),$B2:E2) F8 =LOOKUP(2,1/(NOT(ISBLANK($B2:F2))),$B2:F2) G8 =LOOKUP(2,1/(NOT(ISBLANK($B2:G2))),$B2:G2) H8 =LOOKUP(2,1/(NOT(ISBLANK($B2:H2))),$B2:H2) I8 =LOOKUP(2,1/(NOT(ISBLANK($B2:I2))),$B2:I2) J8 =LOOKUP(2,1/(NOT(ISBLANK($B2:J2))),$B2:J2) K8 =LOOKUP(2,1/(NOT(ISBLANK($B2:K2))),$B2:K2) L8 =LOOKUP(2,1/(NOT(ISBLANK($B2:L2))),$B2:L2) M8 =LOOKUP(2,1/(NOT(ISBLANK($B2:M2))),$B2:M2) N8 =LOOKUP(2,1/(NOT(ISBLANK($B2:N2))),$B2:N2) O8 =LOOKUP(2,1/(NOT(ISBLANK($B2:O2))),$B2:O2) P8 =LOOKUP(2,1/(NOT(ISBLANK($B2:P2))),$B2:P2) Q8 =LOOKUP(2,1/(NOT(ISBLANK($B2:Q2))),$B2:Q2) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>