Solved

EXCEL IF FORMULA NEEDED

Posted on 2014-07-31
9
136 Views
Last Modified: 2014-08-01
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!
0
Comment
Question by:vpopper
  • 5
  • 3
9 Comments
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 40232719
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
 

Author Comment

by:vpopper
ID: 40232777
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
 
LVL 13

Expert Comment

by:Santosh Gupta
ID: 40232803
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
 
LVL 13

Expert Comment

by:Santosh Gupta
ID: 40233976
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:vpopper
ID: 40233984
=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
 

Author Comment

by:vpopper
ID: 40233993
Please see above comments - I have decided to make it a requirement to add "-" to the input entries.
0
 
LVL 13

Accepted Solution

by:
Santosh Gupta earned 500 total points
ID: 40234047
Hi, use below formula for PO.

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

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

Author Comment

by:vpopper
ID: 40234274
Works Perfect!!!  Thanks!
0
 

Author Closing Comment

by:vpopper
ID: 40234278
Two formuals provided by Gupta - both work GREAT!  Awsome Job - Thank you!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
In 2017, ransomware will become so virulent and widespread that if you aren’t a victim yourself, you will know someone who is.
Viewers will learn how to create a PivotTable and make basic changes to it in Excel 2013.
Viewers will learn how to share Excel data with others from desktop Excel, as well as Excel Online via OneDrive, and embed an Excel file on a website.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now