Trouble with VLOOKUP with same values in first column but different values in column_index

Dear,

I have trouble with an excel datafile. I am trying to use a VLOOKUP function but it simply does not work. I have attached the file so I will walk you through my file.
In the 'Main' sheet I am trying to find the correct CIK (Column F) codes by using the Identifier (Column E). I do this by using a VLOOKUP from the sheet 'Uitgebreid'. In the sheet 'Uitgebreid' the identifier is mentioned in column B and the CIK code in Column D. As you can see in the example that I gave a red border it only finds the "87588" number in the 'Main' sheet but as you can see from the red highlited part in 'Uitgebreid' this company probably changed its name in 2001 and their CIK code changed (Identifier stayed the same).
My question:
- How do I get these both values "87588" and "1135431" in the 'main' sheet?

Thanks in advance.

Regards,

Daan

I am open for everything.. I just need this solved haha

Really helpful guys. I think I go with the last methods, this seems the easiest way. The only thing is I have some #N/As in the data. I see that the quarters and years do not correspond but I will manage this myself.