# compare cells

##### Board Regular
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!

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### NBVC

##### Well-known Member
Try something like:

=VLOOKUP(SUBSTITUTE(SUBSTITUTE(A2,"-",""),"+",""),\$H\$1:\$I\$4,2,0)

Where H1:I4 houses your lookup table and A2 houses the first Bank to look up.

Replies
0
Views
247
Replies
6
Views
190
Replies
7
Views
577
Replies
1
Views
510
Replies
5
Views
402