Need look up help

[Meaku15]

Hi all,

My data as following..
Column A will have names.

A1 : alex
A2 : steven
A3: Josh
A4: Josh
A5:Josh

Column d will have time.
A1: 2pm
A2:3pm
A3:1pm
A4:3pm
A5:5pm

On another sheet, I have the names column a First cell a1 is Josh. I want to return in cell b2 5pm then cell b3 1pm.

Let me know if this is clear. Thanks.

 

[AlanY]

try this

Excel 2012
	A	B	C	D	E	F	G
1	alex	14:00		Josh	13:00	17:00
2	steven	15:00
3	Josh	13:00
4	Josh	15:00
5	Josh	17:00
6
7
Sheet2

Cell Formulas
Range	Formula
E1	{=MIN(IF(A1:A5=D1,B1:B5))}
F1	{=MAX(IF(A1:A5=D1,B1:B5))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Meaku15]

try this

Excel 2012

	A	B	C	D	E	F	G
alex	Josh
steven
Josh
Josh
Josh

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

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

[TD="align: right"]13:00[/TD]
[TD="align: right"]17:00[/TD]
[TD="align: right"][/TD]

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

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

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

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

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

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

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

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

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

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

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

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E1[/TH]
[TD="align: left"]{=MIN(IF(A1:A5=D1,B1:B5))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F1[/TH]
[TD="align: left"]{=MAX(IF(A1:A5=D1,B1:B5))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

Thanks man. This is exactly what I needed. I have another question related. Do I need to open up another thread?

Here it goes..

Say I wanna return a specific time.. Let say Josh had timings from 8am, 10am,12pm,1pm,5pm. Is there a way to return the value between 12pm to 2pm and give the difference?in this instance, it will be 1hr. Thanks