Look for nearest column to the right that contains data

[Minglee]

I need to add a formula in Column B that looks for the nearest column containing data to the right of Column B, and then pulls that data into the Column B cells.
For reference: Cell B2 should return the date from C2, Cell B3 should return the data from D3 (as C3 is blank). Is this an Index formula, or ?

 

[Dave87]

Its long winded but this should work - =IF(ISBLANK(C2),IF(ISBLANK(D2),IF(ISBLANK(E2),IF(ISBLANK(F2),IF(ISBLANK(G2),IF(ISBLANK(H2),"Error",H2),G2),F2),E2),D2),C2)

 

[AhoyNC]

Try:
This is an array formula that must be entered with CTRL-SHIFT-ENTER.

Book1
	A	B	C	D	E	F	G
1
2		5	5	10	15	25
3		22			22	6	8
4		12		12	28	14
Sheet4

Cell Formulas
Range	Formula
B2:B4	B2	=INDEX($C2:$M2,MATCH(FALSE,ISBLANK(C2:M2),0))

 

[Minglee]

Try:
This is an array formula that must be entered with CTRL-SHIFT-ENTER.

I like this.. works perfectly, thank you!

 

[AhoyNC]

You're welcome. Thanks for the feedback and welcome to the forum.