Compare fields in two sheets ... return associated data to third sheet

**delirium** · 07-30-2010, 11:39 AM

Spreadsheet attached ...

Object of worksheet <Compiled> is to place Remedy!G (Modified By) into Compiled!H (Remedy Ticket) when Remedy!F (Created By) & Remedy!G (Modified By) match Transfers!B (Login) & Transfers!E (Destination Agent)

I was attempting to modify the code suggestion in the following thread http://www.excelforum.com/excel-prog...copy-data.html Solved by ratcat and DonkeyOte.

Because this data set could become very large, I opted for DonkeyOte's solution:

I put the following code in Compiled!G

=SUBSTITUTE($B3&"@"&$D3," ","")

and this in Compiled!H:

=LOOKUP(9.99E+307,CHOOSE({1,2},0,IF(VLOOKUP(Remedy!F2&"@"&Remedy!G2,Remedy!G:G,1)=$B3&"@"&$D3,INDEX(Remedy!G:G,MATCH($B3&"@"&$D3,G:G)))))

I am confused about the value in the Lookup function ... honestly i'm not convinced the Lookup function is the right choice. There is also one catch. There may be multiple matches ... and although there is another reference column the Date Time columns will not match precisely.

FROM TRANSFERS TAB
AbHernan 7/17/2010 23:00
AbHernan GCohen 7/19/2010 20:07
AbHernan RMejia 7/19/2010 23:27
AbHernan JMarisca 7/22/2010 19:41
AbHernan RMejia 7/19/2010 19:36
AbHernan JAhmadni 7/17/2010 19:36
AbHernan RMarquez 7/17/2010 19:52

FROM REMEDY TAB
AbHernan RMEJIA 7/19/2010 19:35
AbHernan JAHMADNI 7/17/2010 19:35
AbHernan RMARQUEZ 7/17/2010 19:51
AbHernan INAV 7/17/2010 22:59
AbHernan JUAPENA 7/18/2010 17:18
AbHernan RMEJIA 7/19/2010 23:26
AbHernan JMARISCA 7/22/2010 19:41

Thanks in advance for any advice you can offer. Just please don't make fun of me cuz i mucked up the code completely. After about an hour of looking at it i start losing comprehension.

emily