Search column upwards for value

[joesef]

Hi All,

Does anybody know of a function(s) i could use that will look for a corresponding value on the same row on a specific cell, and if a value doesn't exist there, it will look upwards until it finds a value.

Thanks,

Hopefully this will illustrate my question a little better...

abc1		returned abc1
abc2		returned abc2
		Nothing corresponding, returned abc2
abc3		returned abc3
		Nothing corresponding, returned abc3
		Nothing corresponding, returned abc3
abc4		returned abc4

<TBODY>
</TBODY>

 

[jim may]

Excel 2010

	A	B
1	abc1	abc1
2	abc2	abc2
3		abc2
4	abc3	abc3
5		abc3
6		abc3
7	abc4	abc4

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

Sheet1​

Worksheet Formulas Cell Formula B1 =A1 B2 =IF(ISBLANK(A2),B1,A2) B3 =IF(ISBLANK(A3),B2,A3) B4 =IF(ISBLANK(A4),B3,A4) B5 =IF(ISBLANK(A5),B4,A5) B6 =IF(ISBLANK(A6),B5,A6) B7 =IF(ISBLANK(A7),B6,A7) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Robert Mika]

Hi All,

Does anybody know of a function(s) i could use that will look for a corresponding value on the same row on a specific cell, and if a value doesn't exist there, it will look upwards until it finds a value.

Thanks,

Hopefully this will illustrate my question a little better...

abc1		returned abc1
abc2		returned abc2
		Nothing corresponding, returned abc2
abc3		returned abc3
		Nothing corresponding, returned abc3
		Nothing corresponding, returned abc3
abc4		returned abc4

<TBODY>
</TBODY>

For Text
=LOOKUP(REPT("z","255"),$A$1:A1)
For numbers
=LOOKUP(9.9999999999999E+307,$A$1:A1)
For both:
=INDEX($A$1:A1,MAX(IF($A$1:A1<>"",ROW($A$1:A1)-ROW($A$1)+1)))
Confirm Control+Shift+Enter

 

[joesef]

Excel 2010

	A	B
1	abc1	abc1
2	abc2	abc2
3		abc2
4	abc3	abc3
5		abc3
6		abc3
7	abc4	abc4

<TBODY>
</TBODY>

Sheet1​

Worksheet Formulas Cell Formula B1 =A1 B2 =IF(ISBLANK(A2),B1,A2) B3 =IF(ISBLANK(A3),B2,A3) B4 =IF(ISBLANK(A4),B3,A4) B5 =IF(ISBLANK(A5),B4,A5) B6 =IF(ISBLANK(A6),B5,A6) B7 =IF(ISBLANK(A7),B6,A7) <TBODY> </TBODY>

<TBODY>
</TBODY>

Ah, that's pretty cool! Thanks for your help. One of them why did i not think of that moment.

Thanks again!