Hello! I have a long list of banks. For every bank there is a rating. The
rating consits of letters and sometimes a plus or minus sign eg AA-, BBB+, A.
On another spreadsheet I have a table that consists of ratings and mean
default rates. The table looks like:

• AAA 0%
  AA 2%
  A 5%
  BBB 11,2%

What I want to do is to get the mean default rate for every bank based on
the rating. The problem is that the ratings for the banks have the plus/minus
also. Thus I want to ignore them and just pick the rating based on the
letters. Any help very much apreciated! Thanks alot!

Not sure if you need to use code. A formula like this might help:

=VLOOKUP(LEFT(B66,LEN(B66)-1),C110,2,FALSE)

This assumes that:
the table is in range C110
the plus or minus sign is always only one character long at the end of
the string
the rating to be tested is located in cell B66

Hope this helps
J


Arne Hegefors wrote:

> Hello! I have a long list of banks. For every bank there is a rating. The
> rating consits of letters and sometimes a plus or minus sign eg AA-, BBB+, A.
> On another spreadsheet I have a table that consists of ratings and mean
> default rates. The table looks like:
>

• > AAA 0%
  > AA 2%
  > A 5%
  > BBB 11,2%
  >

>
> What I want to do is to get the mean default rate for every bank based on
> the rating. The problem is that the ratings for the banks have the plus/minus
> also. Thus I want to ignore them and just pick the rating based on the
> letters. Any help very much apreciated! Thanks alot!