I have 5000 survey respondents in Sheet 1 with answers in columns C:BH

I want to lookup the answers in each column and apply a weights (scores) stored in Sheet 2.

So, if Respondent A, answers Yes to Q1, then apply a score of +10, if No then apply a score of -10

I have 169 different answers all with different scores.

I have created six columns of weights in Sheet 2, creating 6 different personality profiles. So, if the answer to Q1 is Yes, apply a weight of +10 (for Personality 1), but apply a weight +5 (for Personality 2) and so on.

=VLOOKUP(C2,'Sheet 2'!$D:$L,2,FALSE) - for the score for Answer 1
=VLOOKUP(D2,'Sheet 2'!$D:$L,2,FALSE) - this is for score for Answer 2 and so on....

I repeat this across 58 columns.

I then repeat all of this 5 times - to get my personalities scores.

The net impact is over 2m vlookups.

When I try and adjust the weights, it takes about 5 minutes for Excel to respond - given that it's making 2m calculations!

I'm looking for a more elegant solution.

Any help greatly appreciated.