Vlookup search function for species names

I have two worksheets with tables in Excel: One Example working sheet with with species names written in column E row 4-41 consisting of a Genera and a species name (like Bellis perennis Linnee1987). I want to search in worksheet Red list species in column E for this name by their: first three letters of the first word (Bel) and the first three of the second word (per) and from Red list species column H I want to add the values to column F and G in the example work sheet. Then search for the next species name. I hope that is clear? Can you help with this? I think I have to use VLOOKUP... but not sure then!?

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

No. Not at all clear. I have made a wild guess. If this is NOT what you want, populate THIS sheet with a few rows of manually calculated answers...

There seems to be only one match...

=IFERROR(INDEX('[Red List species List.xlsx]Tabelle1'!$E:$E,1/(1/SUMPRODUCT(ISNUMBER(SEARCH(LEFT(C2,3),'[Red List species List.xlsx]Tabelle1'!$E$2:$E$34))*ISNUMBER(SEARCH(MID(C2,SEARCH(" ",C2)+1,3),'[Red List species List.xlsx]Tabelle1'!$E$2:$E$34))*ROW('[Red List species List.xlsx]Tabelle1'!$E$2:$E$34)))),"")