Cells value bases on array value

Bonjour,
I have a challenging project here and the problem is, I just don't know how to do it.

I need to build a team by choosing team members and wanted abilities for the said team.

Language barrier doen't help me at all here so I will go with complete description.

Team members (thefe, fighter, mage, priest)
Abilities (dodge, combat, magic, faith)

Team score is based on the chosen abilities, you have to choose 3 out of 4 abilities (could be 5 or 6 abilities but I limited the example to 4)
Team is build up of 3 members

The team score is based on the sum of the average ability score for all three abilities. The first choosen ability counts for 1.5 and the other counts for 1.2.

So, on to the problem now

I've created two lists, teammember and ability.

I have a table where you choose three members (B4:B6) and three abilities (C3:E3) (dropdown box).

I want the cells, in the table, to fillup, based on the abilitie and memebers choosen. Keep in mind that the order of abilities impact the team score as Ability 1 (C3) counts for 1.5 and Ability 2 and 3 (D3), (E3) counts both for 1.2.

Also, members don't have ability score in all abilities (ie. Thief have no faith, fighter have no magic)

In the spreadsheet I provid here you will find a table with ability score for each member.

How can I accomplish this?

Thanks for you help and don't hesitates to ask questions, I really want this to work.

Ooops I did it again ...
... found the answer to my own question ...

Please Login or Register  to view this content.

I used the IF function so that when a matched cell is empty, excel does not return a "0" with it does without it.

You have to replace, in the VLOOKUP functions, the reference to B5 (members) and C3 (ability) to reflect the correct cells when you copy/paste this code to the different cells of the table.

Thanks to anyone that looked at that and would have answer.

I just hope this will eventually be usefull to anyone besides me.

Regards,