I get a #REF error when there are identical entries
I assume this is due to the INDEX ROW functions?

What's probably happening is when you have more than one instance the
SUMPRODUCT is summing the multiple row numbers and the total is outside the
indexed range.

If you're interested in *either* the first instance or the last instance you
can replace SUMPRODUCT with MAX. This will make the formula an array
formula**.

=IF(SUMPRODUCT((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A $1))*(DAY(DOA)=B$2))=0,"",INDEX(PORTTIME,MAX((SHIP CODE=$A3)*(MONTH(DOA)=MONTH($A$1))*(DAY(DOA)=B$2)* (ROW(DOA)-1))))

Also, you can put the row offset correction outside the MAX function. This
saves from calculating an array of row offsets when you only need to
calculate one row offset.

=IF(SUMPRODUCT((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A $1))*(DAY(DOA)=B$2))=0,"",INDEX(PORTTIME,MAX((SHIP CODE=$A3)*(MONTH(DOA)=MONTH($A$1))*(DAY(DOA)=B$2)* ROW(DOA))-1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.


--
Biff
Microsoft Excel MVP


"seapilot" wrote in message
...
Greetings,
I have a formula to extract data from a 3 column worksheet (SKED) into
monthly calendar worksheets (MAY, JUNE, etc.).

=IF(SUMPRODUCT((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A $1))*(DAY(DOA)=B$2))=0,"",INDEX(PORTTIME,SUMPRODUC T((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A$1))*(DAY(DOA )=B$2)*(ROW(DOA)-1))))

Formula searches source worksheet for values that match the SUMPRODUCT
conditions of SHIPCODE,MONTH, DAY and displays the value in the PORTTIME
column for the matching ROW. If no match, then cell remains blank.

My problem is I get a #REF error when there are identical entries (i.e.
same
dates) in the DOA column in source sheet that have different values in the
queried PORTTIME column. I assume this is due to the INDEX ROW functions?
There are at most only two duplicate date entries in source DOA column for
any particular SHIPCODE value. I would like to tweak the formula so the
cell
in the calendar sheet displays the first (or second) PORTTIME value
instead
of #REF. What can I do to make this happen? I have gotten lost in
VLOOKUP,
MATCH, etc. and am at a loss.

Source worksheet has 2350 rows and 3 columns. Column heads (correspond to
the named ranges in the formula) are DOA,SHIPCODE,PORT. DOA (date of
arrival)
(mm/dd/yyyy), other two are text.
Calendar worksheets have the MONTH data in $A$1 (mm/dd/yyyy), and ship
names
(corresponding to values in SHIPCODE column in the source worksheet)
beginning in $A$2. Days of month are in $B$2:$B$AF, so cell $B$2 contains
"1". Formula starts in cell B3 in calendar sheet and is filled across the
day columns and down the ship column. This means the formula is in 2294
cells
per monthly sheet. Cell calculation takes time, but is done very
infrequently as there are rarely changes to source sheet.

I have spent a lot of time searching various sites for an answer to this,
and have learned much about SUMPRODUCT, but cannot figure this out. I
would
appreciate some guidance or suggestions.

Seapilot