How to populate from one cell if another is empty, multiple times?

[kellyfirth]

If cell A1 says: #N/A N/A then populate from A2. However if A2 also says: #N/A N/A then populate from cell A3. If A3 is also saying: #N/A N/A then populate from A4.

I'm able to do this for just one switch, so I can get it to populate from A2 if A1 says it. But I'm not sure how to do it for all these cells? Does this make sense? All help is very appreciated

 

[martindwilson]

2 ways maybe
=LOOKUP(2,1/(A1:A4<> "#N/A N/A")*(A1:A4<>""),A1:A4)
=IFERROR(INDEX(A1:A4,MATCH("#N/A N/A",A1:A4)+1),A1)

 

[kellyfirth]

This half works but I need it to prefer the cell A1 over A4. If there is data in both A1 and A4, it is currently picking up A4

 

[martindwilson]

not what you asked! try this array entered formula
=IFERROR(INDEX(A1:A4,MATCH(1,(A1:A4<>"#N/A N/A")*(A1:A4<>""),0)),"sorry all are #N/A N/A or blank ")

 

[kellyfirth]

Apologies, I wasn't sure how to phrase it! This still isn't working, it's coming up with the 'sorry all are #N/A N/A or blank' when there are numbers in some of the cells?

 

[martindwilson]

Excel Workbook
	A	B	C	D	E	F
1	#N/A N/A	#N/A N/A	10	#N/A N/A	t	#N/A N/A
2	1	#N/A N/A	#N/A N/A
3		2	y	z	u	3
4		g	#N/A N/A	#N/A N/A		#N/A N/A
5				11
6
7
8	1	2	10	z	t	3
Sheet4

 

[kellyfirth]

Thank you! This worked with the ctrl, shift & enter. Much appreciated and apologies for taking up your time!