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...
[TABLE="width: 500"]
<TBODY>[TR]
[TD]abc1[/TD]
[TD][/TD]
[TD]returned abc1[/TD]
[/TR]
[TR]
[TD]abc2[/TD]
[TD][/TD]
[TD]returned abc2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Nothing corresponding, returned abc2[/TD]
[/TR]
[TR]
[TD]abc3[/TD]
[TD][/TD]
[TD]returned abc3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Nothing corresponding, returned abc3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Nothing corresponding, returned abc3[/TD]
[/TR]
[TR]
[TD]abc4[/TD]
[TD][/TD]
[TD]returned abc4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

 

[jim may]

Excel 2010

	A	B
abc1	abc1
abc2	abc2
abc2
abc3	abc3
abc3
abc3
abc4	abc4

<colgroup><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B1[/TH]
[TD="align: left"]=A1[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]=IF(ISBLANK(A2),B1,A2)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B3[/TH]
[TD="align: left"]=IF(ISBLANK(A3),B2,A3)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B4[/TH]
[TD="align: left"]=IF(ISBLANK(A4),B3,A4)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B5[/TH]
[TD="align: left"]=IF(ISBLANK(A5),B4,A5)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B6[/TH]
[TD="align: left"]=IF(ISBLANK(A6),B5,A6)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B7[/TH]
[TD="align: left"]=IF(ISBLANK(A7),B6,A7)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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...
[TABLE="width: 500"]
<TBODY>[TR]
[TD]abc1
[/TD]
[TD][/TD]
[TD]returned abc1
[/TD]
[/TR]
[TR]
[TD]abc2
[/TD]
[TD][/TD]
[TD]returned abc2
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Nothing corresponding, returned abc2
[/TD]
[/TR]
[TR]
[TD]abc3
[/TD]
[TD][/TD]
[TD]returned abc3
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Nothing corresponding, returned abc3
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Nothing corresponding, returned abc3
[/TD]
[/TR]
[TR]
[TD]abc4
[/TD]
[TD][/TD]
[TD]returned abc4
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

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>
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

</TBODY>

Sheet1​

[TABLE="width: 85%"]
<TBODY>[TR]
[TD]Worksheet Formulas
[TABLE="width: 100%"]
<TBODY>[TR="bgcolor: #dae7f5"]
[TH="width: 10"]Cell
[/TH]
[TH="align: left"]Formula
[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #dae7f5"]B1
[/TH]
[TD="align: left"]=A1
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #dae7f5"]B2
[/TH]
[TD="align: left"]=IF(ISBLANK(A2),B1,A2)
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #dae7f5"]B3
[/TH]
[TD="align: left"]=IF(ISBLANK(A3),B2,A3)
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #dae7f5"]B4
[/TH]
[TD="align: left"]=IF(ISBLANK(A4),B3,A4)
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #dae7f5"]B5
[/TH]
[TD="align: left"]=IF(ISBLANK(A5),B4,A5)
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #dae7f5"]B6
[/TH]
[TD="align: left"]=IF(ISBLANK(A6),B5,A6)
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #dae7f5"]B7
[/TH]
[TD="align: left"]=IF(ISBLANK(A7),B6,A7)
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
</TBODY>[/TABLE]

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

Thanks again!