Excel function_search data from 1st in the 2nd file files

[nefertitinka]

Dear all,

Hereby my question about data search in two excel work files by logical function.

1st file: I have data about company identification codes and event date (date of acquisitions of other companies)

Acquiror
Code DateAnnounced
130042 03/06/2000
130062 06/15/2009
130062 04/14/2008
130086 03/12/2008
130086 08/15/2006
130086 05/06/2004
130286 10/19/2005
130286 01/21/2005
130286 03/03/2003
130286 11/13/2002
130286 09/18/2000
130286 06/05/2000
130286 05/08/2000
130291 10/04/2007
130291 12/11/2006

2nd file: I have data about the company identification codes, event date + share prices on the event date.

Acquirer
Code Event date Share price
130042 3/14/2006 27.14
130062 4/14/2008 105.18
130079 8/4/2008 1.37
130086 3/12/2008 40.68
130086 8/15/2006 39.44
130113 3/17/2008 12.2
130286 5/8/2000 33.5
130286 9/18/2000 30.125
130286 6/5/2000 37.625
130291 10/4/2007 22.8
130291 10/25/2004 9.6
130298 6/14/2001 11.19
130316 7/15/2004 21.84
130316 8/5/2002 15.96
130316 7/23/2002 15.4
130316 1/8/2004 25.06

I would like to search and link the share prices on the event date to the first file (because the 2 lists have different number and order of the companies).

In order to do it I used VLOOKUP function. But due to the fact that most of the companies are repeated several times in the 2 files (due to several acquisitions), VLOOPUP does not link all of them correctly. Is there a function where I can search the share price based on combination of 2 arguments (company identification code + event date)?

Thank you very much in advance!

Kind regards,
Titi

 

[Andrew Poulsom]

What should be returned if the dates in the first table don't appear in the second?

 

[Misca]

If you know the exact event dates you can use something like:

=INDEX($K$2:$K$17,MATCH(A2&VALUE(B2),INDEX($I$2:$I$17&VALUE($J$2:$J$17),,0),FALSE))

Where your lookup table is found in I2:K17.

The formula looks for exact match for the Code & Event Date combination and returns the value from column K.

If you only know the rough dates you should start by grouping your lookup table by the company ID and date and then you could use the same formula without the FALSE.

 

[nefertitinka]

@ Andrew: The thing is that the only argument in the VLOOKUP was the company identification code, which was repeated several times for serial acquirers. So the company code was the 1st condition and I needed formula to fulfill the second condition and match the date as I had same codes with different dates.

@ Misca: Thank you very very much! Works like a charm )

Best regards,
Titi