Most of the lookups have only one instance and I would like a formula that I
can fill across and down that will still return valid info in those cases. I
need a formula that will work with both one instance and also with 2
instances, like the MAX flavor. Could I fold the IF statement into an
ISERROR function of some kind?
My thought is to insert a row in the calendar (output) sheet where I can
insert and fill the formula amended to display the correct "one instance"
text, and, in the "two instance" cases, display the other of the two
instances not shown in the cell above. I am trying to somehow, get all the
data to display with filled formulas, and avoid the #REF errors.

"T. Valko" wrote:

If there are only one or two instances then you can replace MAX with LARGE:

MAX((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A$1))*(DAY(D OA)=B$2)*ROW(DOA))


LARGE((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A$1))*(DAY (DOA)=B$2)*ROW(DOA),n)

Where n = 1 or 2

For a lookup value that has only one instance using n=2 will return an
error.


--
Biff
Microsoft Excel MVP


"seapilot" wrote in message
...
Thanks! I tried the second suggestion, with the row offset outside the
MAX
function.
Can I amend the formula further to select which of the two instances I
want
returned? MAX returns the first instance alphabetically, which was
actually
the second occurance in the order the data in PORTTIME is presently
sorted.
I would like to see what function is flexible enough.
Seapilot

"T. Valko" wrote:

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