Thread: Vlookup Question - how to ignore - or + in middle of a string

Hi,

I am using Vlookup to label Bonds as either Investment Grade or Junk. My Vlookup table is below.

Some bond grades show up as

BBB-*-

The *- denotes a potential downgrade (or upgrade if is *+)

The operator (- or +) before the *- seems to create a problem and the vlookup table can't process the first (- or +) or it throws an N/A. I think it reads the operator as a plus or minus, thinking it is trying to a math function. I have tried formatting the text on both the cell and the table but that doesn't work. Any ideas on how to ignore the + or - before a * if one exists? This is my first post so I apologize for any lack of ediquette.


Here is the formula I am using for the
=IF(ISNA(VLOOKUP(E26,S_P,3,FALSE)),"Not Rated",VLOOKUP(E26,S_P,3,FALSE))



S_P table
Col A Col B
AAA Investment Grade
AA+ Investment Grade
AA Investment Grade
AA- Investment Grade
A+ Investment Grade
A Investment Grade
A- Investment Grade
BBB+ Investment Grade
BBB Investment Grade
BBB- Investment Grade
BB+ Junk
BB Junk
BB- Junk
B+ Junk
B Junk
B- Junk
CCC+ Junk
CCC Junk
CCC- Junk
CC Junk
D Junk

Originally Posted by DonkeyOte

* in a VLOOKUP acts as a wildcard

Perhaps you want:

VLOOKUP(LEFT(E26,FIND("*",E26&"*")-1),S_P,3,FALSE)

That seems to be exactly what I am looking for, thank you very much.