Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
display first or second match instead of #REF in INDEX ROW formula
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
INDEX MATCH formula | Excel Worksheet Functions | |||
Index / Match in formula | Excel Worksheet Functions | |||
INDEX MATCH formula | Excel Worksheet Functions | |||
INDEX and MATCH in one formula... | Excel Worksheet Functions | |||
How do I display more than one match in a Index/Match formula? | Excel Worksheet Functions |