Solved

EXCEL IF FORMULA NEEDED

Posted on 2014-07-31
9
133 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 39

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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Short answer to this question: there is no effective WiFi manager in iOS devices as seen in Windows WiFi or Macbook OSx WiFi management, but this article will try and provide some amicable solutions to better suite your needs.
In  today’s increasingly digital world, managed service providers (MSPs) fight for their customers’ attention, looking for ways to make them stay and purchase more services. One way to encourage that behavior is to develop a dependable brand of prod…
Viewers will learn how to customize the ribbon and quick access toolbar in Excel 2013.
Viewers will learn a basic data manipulation technique of unpivoting data in Power Query for Excel 2013 and the importance of using good data. Start with data in a poor structure: Create a table on your data: Unpivot columns: Rename columns: …

762 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

18 Experts available now in Live!

Get 1:1 Help Now