display first or second match instead of #REF in INDEX ROW formula

[seapilot]

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,SUMPRODUCT((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

 

[T. Valko]

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((SHIPCODE=$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((SHIPCODE=$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.

 

[seapilot]

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]

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

MAX((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A$1))*(DAY(DOA)=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.

 

[seapilot]

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]

Yeah, you can wrap the formula in ISERROR to trap any errors. One thing to
consider is that the formula is *already* fairly calculation intensive. What
version of Excel are you using?

You can replace the first SUMPRODUCT with the error test.

You can use the LARGE version to increment n but the problem with this is
that n needs to "reset" for each different lookup value as you copy the
formula down a column. You could do that in the formula but now the formula
is probably getting out of hand calculation-wise.

How many unique lookup values are there?

 

[seapilot]

Thank you for your extreme patience
I am using Excel 2007 on Vista 64bit with 4 GB ram and Intel Core 2 Duo p8400.
RE: # of unique lookup values, on source sheet, there are 2250 rows of
data; there are 36 unique values in the SHIPCODE column, perhaps 16 unique
values in the PORTTIME column (although these values are concantenated from 3
other columns to get a value such as "GB 07:00-16:00" from SHIP, TOA, DOA)
and in cases of the #REF error, there are, as I said only 2 instances of
identical DOA values per unique SHIPCODE value with 2 PORTTIME values; ie at
most, 2 same dates (DOA) where a ship(SHIPCODE) visits more than one port
(PORTTIME) but never more than 2 ports, on a given day, which may occur for
that ship one day each week. There are at most, 6 different PORTTIME values
in the entire range that are at issue with the #REF error.
You are correct about the calc getting a bit out of hand. I wonder if
perhaps addressing the issue at the source table level might simplify things?
I could insert a new column of data if it would help, like to assign a
unique key to the DOA values? All I am really interested in is getting the
output to be the values in the PORTTIME range for the correct DOA and
SHIPCODE. I end up having 2 rows per 'ship' on the output sheet anyway in
the $A column anyway, so I have 72 rows of formulas for the 36 unique
SHIPCODE values. for, say, 4 contiguous cells with lookup values, only 1
would have the #REF error and if I could get the output sheet to display the
one LOOKUP value on the first row, and then the other value on the row below
it (with the other lookup values that did not return #REF unchanged from the
row above) I would have a product that meets my needs.
Is there no simpler combination of functions that allow for duplicate dates
and will allow the formula to display the first match on the top row and a
similar formula to display the second match on the row below? I've been
assuming that I am missing something elementary and it is due to my lack of
knowledge regarding functions.
Do I need to embark on a deeper level of learning to solve this? VB?
Access? Solver?
I am willing to go out and learn, I am just a bit ignorant at this point.
Seapilot

 

[T. Valko]

Can you send me a copy of your file so I can see what you're trying to do?
If you can do that I'd like to see an example of where you're getting the
#REF! error. This will help me to understand how the duplicate instances are
causing the problem.

If you want to do that I'm at:

xl can help at comcast period net

Remove "can" and change the obvious. If the file is bigger than 1mb, zip it.

 

[seapilot]

Thanks for looking over the file. The formula you sent worked perfectly! I
will be able to adjust it for other uses with the same style source worksheet
and tweaked for slightly different applications.
Thank you for your patience,
Mark this one SOLVED!
seapilot

 

[T. Valko]

You're welcome. Thanks for the feedback!