excel lookups with text

Water Cooler
#1

any idea how to do a lookup with text values. for example i want to pull a value based on credit rating (Aa1: 0.15; Aa2: 0.30). using a vlookup i get a number and the formula works for some of the ratings but not others. i think its not pulling the right number becuase it doesn’t know how to recognize certain text and number combinations, for instance whenever it’s trying to pull a value for Aaa, Aa1, Aa2, Aa3 it keeps pulling the value for a rating of A3. weird thing is that for whatever reason all the other ratings (A1 to Baa3) work.

is there a better way to do a text lookup can’t figure out why it will recognize some, but not others.

#2

Probably just need to add a “,false” to the end of your lookup formula.

#3

As long as the table in which you are referencing only has one instance/value of each credit rating, you can use sumif or sumifs.

#4

there it is. thanks!

#5

feel real dumb i didnt try that…

#6

vlookup is my friend!!!

#7

You can also use vlookup to convert the text to numbers, calculate averages or such, and convert back to them back to text.