Model with weights trying to perform 2m vlookups!

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.

Sorry - you're absolutely right. Please see attached. Model and Weights Query.xlsx

This may be a little overkill, but this allows you to change the Answer number (in range BK1:DP1) and will still give you the correct answer.

In BK2:

=XLOOKUP(BK$1&XLOOKUP(BK$1,$C$1:$BH$1,$C2:$BH2,,0),Sheet2!$B$2:$B$17&Sheet2!$C$2:$C$17,INDEX(Sheet2!$D$2:$I$17,,MATCH($BJ$1,Sheet2!$D$1:$I$1,0)),0,0)

(copy down and across)