Searching down column and displaying value?

[Aitch]

[TABLE="class: grid, width: 100, align: center"]
<tbody>[TR]
[TD]Tom[/TD]
[TD]Richard[/TD]
[TD]Harry[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Richard[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Harry[/TD]
[/TR]
</tbody>[/TABLE]


What formula do I put in the top row - which searches down that column for a non-empty cell, and displays the result?

I'm having no luck with the LOOKUP commands...

Thanks for the help!

 

[Aitch]

I must be doing something wrong!

After I customise your formula it doesn't show SAME when there is more than 1 match?

=IF(INDEX(AK81:AK105,MATCH(MIN(IF((AK81:AK105<>""),$AM$81:$AM$105)),$AM$81:$AM$105,0))=0,"Same",INDEX(AK81:AK105,MATCH(MIN(IF((AK81:AK105<>""),$AM$81:$AM$105)),$AM$81:$AM$105,0))



Would there be a way to use your VLOOKUP formula above, to display 2 when there are 2 non-empty cells?

 

[Fluff]

Are your blank cells actually empty?

 

[Aitch]

They have an IF function which either shows the name or ""

I thought "" was considered empty by excel?

 

[Fluff]

If there is something in the cell then it's not "empty"
try
=IF(INDEX(AK81:AK105,MATCH(MIN(IF((AK81:AK105<>""),$AM$81:$AM$105)),$AM$81:$AM$105,0))="","Same",INDEX(AK81:AK105,MATCH(MIN(IF((AK81:AK105<>""),$AM$81:$AM$105)),$AM$81:$AM$105,0))

 

[Aitch]

I'm still having problems - which I'm sure is my fault lol!

I think making things simpler would be better for my level of understanding haha!


Is there a way to use your VLOOKUP formula from post 2 which displays the name if there is a non-empty cell
=VLOOKUP("*",A2:A11,1,0)

If there is more than one non-empty cell in the column, it display the number of non-empty cells?

Thanks again!

 

[Fluff]

How about
=IF(COUNTIF(A2:A6,"?*")>1,COUNTIF(A2:A6,"?*"),VLOOKUP("?*",A2:A6,1,0) )

 

[Aitch]

Awesome!