need help with an if statement

**Ceeseven** · 05-12-2009, 05:03 PM

cell g36 contains a "reference #"

"reference number" contains 2 letters folloed by 8 digits

for example cv09050102 or hd09050549 or gm09110201

i need a if statement that will display in diiferent cell a name based my reference #

all cv will be "Chris V"
hd = Harley Davidson
gm = George Michael

**JBeaucaire** · 05-12-2009, 05:16 PM

A worksheet formula to accomplish this is simply:

=LOOKUP(LEFT(G36,2),{"cv","gm","hd"},{"Chris V","George Michael","Harley Davidson"})

Notice in the two arrays, the first array HAS to be sorted properly.

**contaminated** · 05-12-2009, 05:27 PM

U can use IF function, but your function'll be too long, depending on the amount of first two digits. I mean now u have three: cv… hd… gm…
But tomorrow there can appear mn.. Df.. and so on.

So I advise u to tcreate small table elsewhere and using simple VLOOKUP show what u want.

see attachment

**Ceeseven** · 05-12-2009, 05:28 PM

awesome thanks

gonna try it right away!!!!!!

**contaminated** · 05-12-2009, 05:29 PM

=vlookup(left(a1,2),$h$1:$i$3,2,0)

**royUK** · 05-13-2009, 02:40 AM

Your post does not comply with Rule 5 of our Forum RULES. We have Seven question forums: Miscellaneous, General, Programming, Worksheet Functions, Charting, Excel 2007 Help and New Users. Please choose the appropriate forum, and post your question in ONLY one forum.

Moved to Functions Forum

**JBeaucaire** · 05-13-2009, 10:02 AM

If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED].

(Also, use the "scales" icon in our posts to leave Feedback, it is appreciated)