How to jump to the next cell down in excel to compare records after logical test returns f

Hi there,

I am in urgent need to create a formula to automatically fill the data on column C(Patentee).

Where Record ID (1-1, 1-2, etc in table 1) are subdivisions of Record ID (1,2,3 in table 2). I need to assign the Patentee name to each and every record.

I have got to the point where I can compare the digits in the two record ID colums but need to jump to the next record in table two if the result of the logical text is false.

Also, how can I compare anything before the "-" as I am using left(recordID, 1) but once I get to decimals etc, the formula isn't good any longer...

IF(left(RecordID, 1)=VALUE(RecordID),Patentee, ???)

Any ideas will be much appreciated!

Cheers,

K

Table 1
Record ID Drug Name Patentee
1-1 Meropenem (Product) ACS Dobfar S.p.A.
1-2 Meropenem Intermediate ACS Dobfar S.p.A.
2-1 Ertapenem (Product) XYZ
2-2 Ertapenem Intermediate XYZ
3-1 Orlistat (Product) KRKA, D.D.
3-2 Orlistat Intermediate KRKA, D.D.
4-1 Erlotinib Intermediate KRKA, D.D.
4-2 Erlotinib (Product) KRKA, D.D.


Table 2
Record ID Drug Name Patentee
1 Meropenem ACS Dobfar S.p.A.
2 Ertapenem XYZ
3 Orlistat KRKA, D.D.