Vlookup and Index/Match problem with multiple rows

[excelismyfriend]

Hello All:

I can't seem to get the right code to deliver cells from either my Vlookup or Index/Match functions on matching dates & times.

The point of the code is to use a date/time from one list (thousands long), look for a match in another list (thousands long), and pull in data from the column next to it in the same row. I have used vlookup many times for looking up data before, but the problem is that if the Data table has multiple entries on the same date/time (this is certainly will happen for my data) the Vlookup and Index/Match functions return only the first entry, at least how I have it coded right now.

I attached a spreadsheet illustrating what I tried with Index/Match and Vlookup, and also have it reprinted in text below.

Any coding tips to bring in data from multiple rows with identical matching lookup criteria (like date & time) would be greatly appreciated!

Index/Match trial:
DATA TABLE
Timestamp_1 Key1 Motor1 Amount1
7/20/09 1:34:54 AM 105 1 730
7/20/09 1:35:04 AM 103 1 260
7/20/09 2:19:34 AM 105 1 255
7/20/09 2:19:34 AM 103 2 705
7/20/09 2:19:34 AM 105 1 -515
7/20/09 2:19:36 AM 105 1 -220
7/20/09 2:19:41 AM 103 1 -835

CHECK TABLE
Timestamp_2 Key1 (code is in attached spreadsheet & below)
7/20/09 1:35:04 AM is 103
7/20/09 1:35:05 AM #N/A
7/20/09 1:35:06 AM #N/A
7/20/09 1:35:07 AM #N/A
7/20/09 2:19:33 AM #N/A
7/20/09 2:19:34 AM is 105
7/20/09 2:19:34 AM is 105
7/20/09 2:19:34 AM is 105
7/20/09 2:19:41 AM is 103

Key1 code: =IF(INDEX($A$3:$D$9,MATCH(A13,$A$3:$A$9,0),2)=103,"is 103",IF(INDEX($A$3:$D$9,MATCH(A13,$A$3:$A$9,0),2)=105,"is 105","not 103 or 105"))


Vlookup trial:
DATA TABLE
Timestamp_1 Key1 Motor1 Amount1
7/20/09 1:34:54 AM 105 1 730
7/20/09 1:35:04 AM 103 1 260
7/20/09 2:19:34 AM 105 1 255
7/20/09 2:19:34 AM 103 2 705
7/20/09 2:19:34 AM 105 1 -515
7/20/09 2:19:36 AM 105 1 -220
7/20/09 2:19:41 AM 103 1 -835

CHECK TABLE
Timestamp_2 Key1 (code is in attached spreadsheet)
7/20/09 1:35:04 AM is 103
7/20/09 1:35:05 AM is 103
7/20/09 1:35:06 AM is 103
7/20/09 1:35:07 AM is 103
7/20/09 2:19:33 AM is 103
7/20/09 2:19:34 AM is 105
7/20/09 2:19:34 AM is 105
7/20/09 2:19:34 AM is 105
7/20/09 2:19:41 AM is 103

Key1 code: =IF(VLOOKUP(A13,$A$3:$D$9,2)=103,"is 103",IF(VLOOKUP(A13,$A$3:$D$9,2)=105,"is 105","not 103 or 105"))

[JBeaucaire]

What SHOULD the answer be in those cells? (B14:B17)

On the VLOOKUP sheet, using this formula will at least put the same errors into the same cells...

=IF(VLOOKUP(A13,$A$3:$D$9,2,0)=103,"is 103",IF(VLOOKUP(A13,$A$3:$D$9,2)=105,"is 105","not 103 or 105"))

=============
EDIT
Your question:
"Any coding tips to bring in data from multiple rows with identical matching lookup criteria (like date & time) would be greatly appreciated!"

....is accompanied by a sample sheet with no identical matching lookup criteria. Perhaps you could mockup a sample shing true duplicates and what you want as results.

[excelismyfriend]

Hello JBeaucaire:

Thank you for the quick response!

What I would like from the Check Table calculations is this:

<----- A - -------><---B---->
7/20/09 1:35:04 AM----103
7/20/09 1:35:05 AM
7/20/09 1:35:06 AM
7/20/09 1:35:07 AM
7/20/09 2:19:33 AM
7/20/09 2:19:34 AM----105
7/20/09 2:19:34 AM----103
7/20/09 2:19:34 AM----105
7/20/09 2:19:41 AM----103

Note: The empty cells in the "B" column in the Check Table represent where no data matched up to the Data Table date/times. I only want data where the time/date match exactly with 1 second resolution, and matching multiple rows of the same date/time. If the duplicates were placed in column "C" and "D" (as below), that format would be ok too.

<----- A - -------><---B----><---C----><---D---->
7/20/09 1:35:04 AM----103
7/20/09 1:35:05 AM
7/20/09 1:35:06 AM
7/20/09 1:35:07 AM
7/20/09 2:19:33 AM
7/20/09 2:19:34 AM-----105------103-------105
7/20/09 2:19:34 AM
7/20/09 2:19:34 AM
7/20/09 2:19:41 AM-----103

I added this set of mockup data to the spreadsheet (attached).

Thanks again!

[JBeaucaire]

In B13:

=IF(NOT(ISNUMBER(MATCH(A13,$A$3:$A$9,0))), "", IF(INDEX($A$3:$D$9,MATCH(A13,$A$3:$A$9,0),2)=103,"is 103",IF(INDEX($A$3:$D$9,MATCH(A13,$A$3:$A$9,0),2)=105,"is 105","not 103 or 105")))


To keep from having to run the same calc over and over, maybe this instead:

=IF(NOT(ISNUMBER(MATCH(A13,$A$3:$A$9,0))), "", LOOKUP(INDEX($A$3:$D$9,MATCH(A13,$A$3:$A$9,0),2),{-999,103,105,106},{"not 103 or 105","is 103","is 105","not 103 or 105"}))

[excelismyfriend]

Hello again JBeaucaire:

I tried the suggestion. The code is still having trouble getting all data from the duplicate rows with the same time value (in my example, 7/20/09 2:19:34 AM has both "103" and "105"). The code returns just the "105" for all three rows with this timestamp.

Any other ideas?

[JBeaucaire]

Ugh...as in ugly, but it seems to be returning the correct results:

=IF(NOT(ISNUMBER(MATCH(A13,$A$3:$A$9,0))), "", IF(INDEX($A$3:$D$9,MATCH(A13,$A$3:$A$9,0)+COUNTIF($A$12:A12,A13),2)=103,"is 103",IF(INDEX($A$3:$D$9,MATCH(A13,$A$3:$A$9,0)+COUNTIF($A$12:A12,A13),2)=105,"is 105","not 103 or 105")))

[DonkeyOte]

Another alternative perhaps based on your sample file, What I Want sheet

Please Login or Register  to view this content.

Apply custom format to B13 onwards of: [=0]"";General

[excelismyfriend]

Sorry for the delay. Thank you for the comments and suggestions. I used both of the sugggestions for a combination that works for me. Both suggestions have merit and do work. My spreadsheet template now updates automatically with each new set of data. Problem solved.

Thanks again experts!!