EXCEL IF FORMULA NEEDED

Hello -

I am seeking a formula (perhaps IF) that would include a v-look up.  The entry field below are entered codes that we use - the letters are important.  Below is a v-look up table or key.  When an entry contains a certain letter (not just starts with) the formulas should return what is shown below in the formula columns, pulled from the v-look table.  I know how to incorporate the v-look into the If statement but I don't know how to handle this...
if the entry contains an I, or a B, or a D, or a J ect.

sort of "like" in an ACCESS Query. Sometimes the entry field letters could be in the front or rear of the numbers.
--------------------------------------------------------------
ENTRY FIELD      FORMULA COLUMN
I-5642                       SHOULD RETURN (Indy-02)
B-8953                       SHOULD RETURN (GIV-10)
D-55486                       SHOULD RETURN (Denver-06)
4953-B                       SHOULD RETURN (GIV-10)
J-568                       SHOULD RETURN (Jacks-09)

---------------------------v-look up table---------------

FROM ENTRY      RETURN DATA
IB                        Bulk-01
I                        Indy-02
R                        Reno-03
DE                        Delta-04
D                        Denver-06
G                        Texas-07
W                        W Allis-08
J                        Jacks-09
IN                        Insource-15
B                        GIV-10
P                        PR-22

Thanks!
vpopperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
are you replacing the J or is it for a new column?

Find can do it for one character:

eg:  

=FIND("f",B1)

If you're going to chain the if's, you need to check for IB before I because I is in IB
0
vpopperAuthor Commented:
The entry fields have to stay as is.  They are identification numbers for internal documents.  One issue is shown below, three of my entry types contain an "I" so we have to tack on a second letter.

ENTRY                 ID CODE IN ENTRY      RETURN THIS
IB-56849                           "IB"                              Bulk-01
I-5698                            "I"                              Indy-02
IN-5842                           "IN"                             Insource-15
            
Do you know how to incorprorate a FIND in a vlook up?
0
Santosh GuptaCommented:
Hi,

using =LEFT(D12,(FIND("-",D12)-1)) formula, assuming your data is in D column. and you want result in E column then put below formula.

 =vlookup(LEFT(D12,(FIND("-",D12)-1)), selectyourrange, 2,0)

pls let me know if it still not works.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Santosh GuptaCommented:
Hi,

For invoice

=LEFT(A1,(FIND(-LOOKUP(0, -MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1))))),A1)-1))

Open in new window


 =vlookup(replacewithaboveformula, selectyourrange, 2,0)

for PO i will update.
0
vpopperAuthor Commented:
=VLOOKUP(LEFT(B24,(FIND("-",B24)-1)), $E$20:$G$30, 3,0)

OK - This formual works GREAT on these internal numbers - that start with letter(s).
IB-5642
B-8953
D-55486
B-5558468
IN-8759

How can the above formuala be modified to do the same vlook on this set of internal numbers (represents a different internal transaction type).  Instead of starting with letter(s), it ends with letter(s).

10848-IB
11085-I
10852-IN
10852-D
10852-J
10835-R
10836-I
10837-IB
10838-D
10839-DE

Thanks
0
vpopperAuthor Commented:
Please see above comments - I have decided to make it a requirement to add "-" to the input entries.
0
Santosh GuptaCommented:
Hi, use below formula for PO.

=RIGHT(D11,LEN(D11)-(FIND("-",D11)))

 =vlookup(replacewithaboveformula, selectyourrange, 2,0)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vpopperAuthor Commented:
Works Perfect!!!  Thanks!
0
vpopperAuthor Commented:
Two formuals provided by Gupta - both work GREAT!  Awsome Job - Thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Spreadsheets

From novice to tech pro — start learning today.