Assume 2 columns of data: due date and project name, sorted by due date
(multiple projects/due date possible). I need to fill a calendar (1
mon/sheet) with the project names on the date they're due. I can use VLOOKUP
to fill in the first cell for the date, but how do I get the second project
name on the second cell for that date? VLOOKUP, as far as I can see will
only deliver the first matching value when I specify FALSE as the last
argument.

Any ideas for using another function? It strikes me that what I need is
something similar to the "next record" field in Word's mail merge. VB
programming is not an option -- not currently a talent of mine. Thx.
--
IdeaRat

VLOOKUP(lookup_value,table_array,col_index_num,ran ge_lookup)

add one column to your table_array range,
add one to the col_index_num

E3: =VLOOKUP(D3,mytable!$A$2:$B$10,2,False)
F3: =VLOOKUP(D3,mytable!$A$2:$C$10,3,False)

and E3 could just as well be
E3: =VLOOKUP(D3,mytable!$A$2:$B$10,3,False)

and mytable!$A$2:C$10 could just as by be definedTable a defined table

VLOOKUP Worksheet Function
http://www.mvps.org/dmcritchie/excel/vlookup.htm

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"IdeaRat" wrote in message ...
Assume 2 columns of data: due date and project name, sorted by due date
(multiple projects/due date possible). I need to fill a calendar (1
mon/sheet) with the project names on the date they're due. I can use VLOOKUP
to fill in the first cell for the date, but how do I get the second project
name on the second cell for that date? VLOOKUP, as far as I can see will
only deliver the first matching value when I specify FALSE as the last
argument.

Any ideas for using another function? It strikes me that what I need is
something similar to the "next record" field in Word's mail merge. VB
programming is not an option -- not currently a talent of mine. Thx.
--
IdeaRat

Hi

if i'm understanding you correctly, you have a list of projects and dates in
the format
.........A............B
1....Date.......Project

with multiple Projects for the same date

VLOOKUP can only return the first match for the given lookup date ...
however, Harald Staff and i have come up with a concatenate if function that
will concatenate (in your case) the project names for the same date,
separating them with a comma, space or whatever
in your calendar

the function is:
---
Public Function CONCAT_IF(ConcCheck As Range, _
ConcCrit As Variant, _
Optional ConcRange As Range, _
Optional DelimitWith As String) As String
'written by JulieD and Harald Staff
'ConcCheck - range to check for the criteria
'ConcCrit - the criteria
'ConcRange - range to concatenation
'DelimitWith - the delimination character(s)

Dim Cel As Range
Dim i As Long, j As Long
Dim checkarray() As String
Dim rangearray() As String

If ConcRange Is Nothing Then Set ConcRange = ConcCheck

i = ConcCheck.Count
j = ConcRange.Count

If i < j Then
Exit Function
End If

ReDim checkarray(i - 1)
ReDim rangearray(i - 1)

i = 0
For Each Cel In ConcCheck
checkarray(i) = Cel.Text
i = i + 1
Next
i = 0
For Each Cel In ConcRange
rangearray(i) = Cel.Text
i = i + 1
Next

For i = 0 To j - 1
If checkarray(i) = ConcCrit Then CONCAT_IF = _
CONCAT_IF & rangearray(i) & DelimitWith
Next
If CONCAT_IF < "" Then _
CONCAT_IF = Left$(CONCAT_IF, _
Len(CONCAT_IF) - Len(DelimitWith))
End Function

---
to use it, right mouse click on a sheet tab, choose view code
in the VBE window choose insert / module
copy & paste the above directly in there
.... use alt & F11 to switch back to your workbook .... then if your dates
and project names are on another sheet to your calendar you'ld need to range
name them, e.g. select the dates, click in the name box (left of formula
bar) and type a name e.g. P_Dates and press ENTER, do the same for the
project names (e.g. P_Names) then go to your calendar
now assuming your calendar is in the format
...................A..................B........... ......C
1..............1/1/05............1/2/05.....1/3/05
2.........want name here..

click in A2, click on the Fx symbol on the toolbar and find the user defined
functions category, choose CONCAT_IF
the parameters you want are

ConcCheck: P_Dates
ConcCrit: A1
ConcRange: P_Name
DelimitWith: ,

click OK and then you can drag the formula across the other dates.

Hope this helps
Please let me know how you go.

Cheers
JulieD

"IdeaRat" wrote in message
...
Assume 2 columns of data: due date and project name, sorted by due date
(multiple projects/due date possible). I need to fill a calendar (1
mon/sheet) with the project names on the date they're due. I can use
VLOOKUP
to fill in the first cell for the date, but how do I get the second
project
name on the second cell for that date? VLOOKUP, as far as I can see will
only deliver the first matching value when I specify FALSE as the last
argument.

Any ideas for using another function? It strikes me that what I need is
something similar to the "next record" field in Word's mail merge. VB
programming is not an option -- not currently a talent of mine. Thx.
--
IdeaRat

IdeaRat wrote:
Assume 2 columns of data: due date and project name, sorted by due date
(multiple projects/due date possible). I need to fill a calendar (1
mon/sheet) with the project names on the date they're due. I can use VLOOKUP
to fill in the first cell for the date, but how do I get the second project
name on the second cell for that date? VLOOKUP, as far as I can see will
only deliver the first matching value when I specify FALSE as the last
argument.

Any ideas for using another function? It strikes me that what I need is
something similar to the "next record" field in Word's mail merge. VB
programming is not an option -- not currently a talent of mine. Thx.

If the functions in the freey downloadable file at
http://home.pacbell.net/beban are available to your workbook

=VLOOKUPS(sought_date, A1:B10, 2) array entered into a column of cells
as long as required will return corresponding project names.

Alan Beban