Auto fill adjacent cells with formula & searching more than one row for a result.

Hello Everyone, I'm a new member to this forum and I hope someone will be able to help me with a couple of issues.

I am in the process of building a recording programme for storing moth records in and I have a couple of problems with formula and I would be very grateful if someone could help me out. To make things easier I have attached a copy of the workbook I'm using.

Problem 1) In columns A,B,C, and E, and from row 4 down of the 'Macro Moths' worksheet you will see there is a formula that refers to data on the 'Index' worksheet. As long as the formula is filled in, in columns A,B,C, and E this data automically fills in once the species common name is entered into column D and this all works fine. However as things stand the formula in columns A,B,C, and E has to be manually extended for the process to work and what I'm wondering is weather it is possible to get the appropriate formula in columns A,B,C, and E to fill in automatically once the common name is entered into column D. I have entered some species on the 'macro' worksheet for you play about with. If this can be done, the same would have to be applied to columns A,B,C and D of the 'Micro Moths' worksheet.

Problem 2) On the 'Micro Moths' worksheet the recorder has to enter the Latin name into column E and once done the data in columns A,B,C and D load automatically. The problem here is that just recently about 200 species have had name changes and therefore, initially, recorders will be using both the new and old names and the recording programme has to be aware of this when a name is entered. The new names are in column E of the 'index' worksheet and those species with older names are in the same row, in column K of the 'index' worksheet. When the Latin name is entered on the 'Micro Moths' worksheet, what I need is a bit of formula which would lookup the name in column E and also the same row in column K and if either name has been entered into column E of the 'Micro Moths' worksheet, then the newer name in column E should be entered as the result, as column E should be the given as the default answer.
You will see that I've entered some Latin names on the 'Micro moths' worksheet in E4:E10 using the new species names and below this I have entered the same seven species using the older names. These seven species on the 'index' worksheet.

Sorry these are such long questions. Thanks very much for any help which can be offered.