Make a list by a formula?
Posted by Nico on December 27, 2001 1:32 PM
Sorry for the poor english...
It is possible with a formula to get a list of items from a range. I have an area with a list of holidays, in the first column stands the name of holidays and in the second column stands the date values. Now I want to lookup for a date value and get all holidays-names for this date. I have a VBA-solution
, but i would like to do it by a formula - but how? My only solution was to make it with the OFFSET-Formula in more then one cell, but the formula is to long. Can somebody help me?
Thanks
Posted by Derek on December 27, 2001 9:08 PM
List holidays names in Column D (leave D1 blank).
List date (eg 28/12/01) in Column E (leave E1 blank).
A1 = 0
C1 = 0
A2 formula is =A1+1
B2 formula is =IF(E2=$F$1,A2,"")
C2 formula is =MIN(OFFSET($B$2,C1,0):$B$33)
Select A2:C2 and scroll formula down
F2 formula is =IF(A2>COUNT($B$2:$B$33),"",VLOOKUP(C2,$A$2:$D$33,4))
Select F2 and scroll formula down.
In F1 type your date, holiday names will list below it.
(If your data uses more than 33 rows increase the 33 in the formulas to the number you require)
