compare cells

nada

Board Regular
Joined
Jul 4, 2006
Messages
193
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!
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,765
Messages
5,574,116
Members
412,572
Latest member
Chriszion
Top