Lookup data in an inconsistent format

[amadeus90]

Hi,

So I am having a problem with looking up data in an inconsistent format. Please refer to the attached image for the example. I have company's code, year, and ROA (Return on Asset). Now I want to find ROA at year t-1 of each company, but here is the key constraint:

Some companies have 5 years of data, some have 8 and so on, but not all of them are consecutive (for example company A may have data for year 2003, 2005, 2006, 2007, 2009). I only want to look up and print out those of year t-1 of the same company, or else print out N/A. I have tried if function but yet been success.

Thank you!

 

[amadeus90]

[/URL] hebergeur d images[/IMG]
My example, sorry for the clumsy post!

 

[circledchicken]

Hi,

Maybe you can try something like this in E2 and copy across and down:

=LOOKUP(
    9.99999999999999E+307,
    IF(
      $B$2:$B$1000 = $B2,
      IF(
        $C$2:$C$10000 = ($C2 - COLUMNS($D$2:D$2)),
        $D$2:$D$10000)))