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.

If I understand correctly that by decimal you mean >=10 and looking at Table 2 based on Table 1

=VLOOKUP((LEFT(A3,FIND("-",A3)-1))+0,$E$3:$G$6,3,0)

Please do not re-post: this is the third posting of this thread.

That's fantastic John, this works, you have saved my day !
Thank you very much!
Katherine

Glad to help. Could you please mark as SOLVED ("Thread tools" on initial post). Thank you.