vlookup and sumproduct combine issue

[vincentwady]

Dear all,

Whether is possible to combine the result return from vlookup with the
sumproduct?

Here is my issue,

A B
Aa1 1000000
Baa1 2000000
Ba1 3000000
Caa1 2500000
..... .....

There is a array code I will need to reflect the rating score, as
below,

Aaa 1
Aa1 10
Aa2 20
Aa3 40
A1 70
A2 120
A3 180
Baa1 260
Baa2 360
Baa3 610
Ba1 940
Ba2 1350
Ba3 1780
B1 2220
B2 2720
B3 3490
Caa1 4770
Caa2 6500
Caa3 8070
Ca 10000
C 10000
D 10000
NR 10000

I did the way to calculate the weight average rating on total
portfolio is I first add one column to vlookup the rating turn into
the rating score, and sumproduct the column B and the rating score and
then devide total of column B. The final step is index the weight
average credit score to return the total portfolio rating, ie. Ba3.

Here is my issue,

How I can don't add one more column but still can do the calculation
to return my weight average rating in one cell? Can I
sumproduct(vlookup(A2:A1000),B2:B1000)/subtotal(109,B2:B1000) to get
the weighted average score? and I can go from here to index the
rating? I seems impossible to vlookup whole array and return each
value to sumproduct another column. And here is my issue.

Thank you so much

Vincent