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!

 

[Fluff]

How about
=VLOOKUP("*",A2:A11,1,0)

 

[Aitch]

Awesome, thank you!

 

[Fluff]

You're welcome & thanks for the feedback

 

[Aitch]

[TABLE="class: grid, width: 100, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]JILL[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]JACK[/TD]
[TD]150[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]JILL[/TD]
[TD]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
No problem bro, you guys rock!

Can your formula be amended so that if the column has two non-empty cells - it looks at the next column and displays the name with the lowest number?

Thanks again!

 

[Fluff]

How about

Excel 2013/2016
	A	B	C
1		Jill
2
3
4		Jack	200
5
6
7		Jill	100
Data

Cell Formulas
Range	Formula
B1	=INDEX(B2:B10,MATCH(MIN(C2:C10),C2:C10,0))

 

[Aitch]

4 minutes to reply - dang dude, are you Superman!

Works great, is there a way to add additional columns like this?

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

When the column shows more than the two corresponding numbers, like above - is there a way for it to only compare the two numbers which relate to the name?
So it checks only:
Jack = 200
Harry = 100
Then displays Harry with the lowest number of the two?

Thanks!

 

[Fluff]

How about

Excel 2013/2016
	A	B	C	D
1	Tom	Richard	Harry
2
3		Richard		300
4			Jack	200
5	Tom			50
6			Harry	100
Data

Cell Formulas
Range	Formula
A1	{=INDEX(A2:A10,MATCH(MIN(IF((A2:A10<>""),$D$2:$D$10)),$D$2:$D$10,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

But this will fail if you have two numbers the same.

 

[Aitch]

If there are two numbers the same - instead of the formula failing, could it display some text like SAME?

Thanks again!

 

[Fluff]

Maybe
=IF(INDEX(A2:A10,MATCH(MIN(IF((A2:A10<>""),$D$2:$D$10)),$D$2:$D$10,0))=0,"Same",INDEX(A2:A10,MATCH(MIN(IF((A2:A10<>""),$D$2:$D$10)),$D$2:$D$10,0)))
confirmed with CSE