[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 145
  • Last Modified:

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!
0
vpopper
Asked:
vpopper
  • 5
  • 3
1 Solution
 
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
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
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
 
vpopperAuthor Commented:
Works Perfect!!!  Thanks!
0
 
vpopperAuthor Commented:
Two formuals provided by Gupta - both work GREAT!  Awsome Job - Thank you!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now