return value in an array for Excel

[mahhogany]

Hi Wonderful people.

I have searched high and beyond but can't seem to find the correct answer to this issue.

I have a list of dates (list 1) and by each date I want to put a formula in a cell that will search the array and return the period number

I have a table which list (list2) an array of dates and their period numbers.

The forumla should check if the date in list 1 is between the dates any of the dates in list 2.

example list 1.
Col.A Col.B
09-Apr-08
10-Aug-08
11-Jun-08
12-Feb-08
13-Apr-05


DatePeriod name



List 2 (array
Col E Col F Col G
30-Mar-08 26-Apr-08 08/09 P1
27-Apr-08 2 4-May-08 08/09 P2
25-May-08 21-Jun-08 08/09 P3
22-Jun-08 19-Jul-08 08/09 P4
20-Jul-08 16-Aug-08 08/09 P5
17-Aug-08 13-Sep-08 08/09 P6

I want the forumla (which will be in col. B) to look at the value in Col A and see if is between any of the dates in the Array in col E:G and return the value in column G.

I have tried a VLookup but that did not work. (=VLOOKUP(A2,E1:G92,3)
I have tried match and index all to no avail.

I would appreciate any help with either some VBA code or an excel formula.

Many thanks!

 

[Brianwarnock]

If you can number the rows by say making H1 to H6 1 2 3 4 5 6
then you can use Sumproduct to find the row then Index and Match like so
remember it is an array formula.

=INDEX(G$1:G$6,MATCH(SUMPRODUCT(--(E$1:E$6<=A1)*(F$1:F$6>=A1)*(H$1:H$6)),H$1:H$6))


Brian

 

[mahhogany]

Hiya

Thanks very much for replying but it didn't work. :-(
Should I have put the formula in the B column?

And I am trying to match the dates in the A column to the array. I need a formula that will return the correct period i.e. (07/08 P1 etc) that the date falls in.

 

[Brianwarnock]

In my test I put the formula in the B column as that is where you appeared to want the result, but you could put it in a different col if that's where you wanted the result, just ensure correct references.
My results are shown below.

Brian

09/04/2008 08/09 P1
10/08/2008 08/09 P5
11/06/2008 08/09 P3
12/07/2008 08/09 P4
13/04/2008 08/09 P1

 

[Brianwarnock]

If you prefer code then a function like

Function fgetperiod(dcell)

'Brian warnock 28/July/2008

For Each c In Range("E1:E6")

If dcell >= c And dcell <= c.Offset(0, 1) Then
tcell = c.Offset(0, 2)
GoTo exitcode
End If

Next c

exitcode:
fgetperiod = tcell

End Function


use =fgetperiod(A1)


Brian

 

[mahhogany]

Thanks very much for the code...I much prefer that and whats more it works! You are great!

 

[Brianwarnock]

Happy to help, the formula does work and i supplied that first as you had not tried code and i wondered if you would be comfortable with it, no offense meant.

Brian

 

[mahhogany]

No offence taken!