pull a value from a range

**hedge** · 11-20-2008, 09:54 AM

Hi,

If I have a range of dates like this -

1/31/09
2/28/09
3/31/09
4/30/09

I would like a formula that would pull me the 2/28/09 value.

I know max would give me 4/30/09 and min would give me 1/31/09.

Is there something to give me 2/28/09 or 3/31/09?

Thanks,

**davesexcel** · 11-20-2008, 09:58 AM

Are you trying to get the last day of a particular month???

**hedge** · 11-20-2008, 09:58 AM

no - just the 2nd or 3rd value in a range.

**davesexcel** · 11-20-2008, 10:07 AM

An offset formula will do something like that

=OFFSET(A1,0,0) '1st value
=OFFSET(A1,1,0) '2nd value
=OFFSET(A1,2,0) '3rd value

If you have the 1st value in A1

**hedge** · 11-20-2008, 10:08 AM

Thanks for the help

**Cheeky Charlie** · 11-20-2008, 10:17 AM

um, I think you need =large(array, k) and possibly =small(array, k) where k is the rank.
i.e.
large(array,1) = max(array)
large(array,2) = second largest of array

HTH

**EdMac** · 11-20-2008, 10:36 AM

I'd go with Charlie there.

dates are just numbers so you can use large and small and then specify the position you want.

**shabbir** · 11-20-2008, 11:11 AM

Hi,

I am stuck with a similar kind of problem. I have got some data in a sheet like this:
Item Date Qty
A 10-10-08 5unit
B 12-11-08 4unit

I have another table with details of all transaction related to items . something like below:
Item Document Date Qty
A GRN-001 05-08-08 10unit
A GRN-081 19-09-08 2unit
A GRN-085 21-10-08 12unit
A GRN-090 04-11-08 11unit
B GRN-021 15-08-08 8unit
B GRN-088 21-09-08 5unit

Now the challange for me is to look for 1st table item into the 2nd table data and get the document no. and date which are immediately before the corresponding date of the item in 1st table.

In other words, for item "A" in 1st table ,I have to get document no. and date from 2nd table which should be GRN-081 and 19-09-08

I tried working with vlookup and date function but in vain.

Plz suggest me some solution how to do this.

Thanks
Shabbir