Lookup problem

[weerdhof2006]

In a column of entries I would like to find the first entry in the column and return the value from another column, but in the same row. The same would apply to the last entry in the column.

Example:

1
2 6
3 7
4 8
5

First entry 6 return value 2
Last entry 8 return value 3

Thanks

[Dav]

Not entirely clear but perhaps, if the data was in b1:b8 as an array (shift, ctrl enter)

=MAX((B1:B8>0)*(ROW(B1:B8))) to return the row numbers

and then an index , if the above formula was in cell B11 something like
=INDEX(B1:B8,B11)

Regards

Dav

[weerdhof2006]

Thanks Dav,

Thats a start, but not quite the expected outcome. I think I wasn't clear in what I wanted. Here is a copy of part of the spreadsheet.

A B
1 FORMULA 2 - finish time
2 FORMULA 1 - start time
3 0600 - 0630
4 0630 - 0700
5 0700 - 0730 1
6 0730 - 0800 1
7 0800 - 0830 1
8 0830 - 0900 1
9 0900 - 0930 1
10 0930 - 1000 1
11 1000 - 1030 1
12 1030 - 1100 1
13 1100 - 1130
14 1130 - 1200
15 1200 - 1230

The first formula should go into B2. It should recognise the first entry (in cell B5) in the array (B3:B15) and return the value 0700 from =LEFT(A5,4)

Then another formula should go into B1, recognising the last entry (in cell B12) in the array (B3:B15) and return the value 1100 from =RIGHT(A12,4).

The only returns I want to see in B2 and B1 are 0700 1100.

Background: I'm working on a staff rota spreadsheet whereby staff are planned with a starting time of a shift and a finish time. Further in the spreadsheet total hours worked are calculated and some more gadgets. The headings (B2 and B1) should just give me the start and finish time of the shift.

Thanks for all your efforts.

Jan

[Dav]

Entered as arrays (holding down shft ctrl and then pressing enter)

b1
=INDEX(RIGHT($A$1:$A$15,4),MAX(ROW($B$3:$B$15)*($B$3:$B$15>0)))

b2
=INDEX(LEFT($A$1:$A$15,4),MIN(IF(ROW($B$3:$B$15)*($B$3:$B$15)>0,ROW($B$3:$B$15),9999)))

Regards

Dav

[weerdhof2006]

Dav,

Happy New Year!

Thank you that was a step in the right direction, but what would the formula look like if all the entries in column B would have the value 1 (rather than 6, 7, 8 etc). In other words: How can I identify the first and the last entry in the column.

Jan

[Dav]

The formula is based on the row number, so it does not matter what the numbers are in each column, the last row number is always the highest. b3:b15>0 just returns a list of true and false so it always returns the biggest row. you would have been much quicker just putting the values in yourself and seeing what happens. Remember they are and array (shft ctrl then enter)

Regards

Dav