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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

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,112,796
Messages
5,542,552
Members
410,560
Latest member
1ndependent
Top