Page 1 of 2 12 LastLast
Results 1 to 15 of 21

Thread: vlookup (2000)

  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    vlookup (2000)

    in the column A i have this string:

    08 03
    AAAA
    15 02
    ...

    in the column B:

    PROVA1
    PROVA
    PROVA2
    PROVA

    I could want make a vlookup in this mode:

    if is present in the first 2 character 2 number and in the column B is present PROVA insert in the column C the word OK if not present this condition in column C insert KO

  2. #2
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: vlookup (2000)

    Is the following table right?
    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center valign=bottom>1</td><td align=right valign=bottom>08 03</td><td valign=bottom>PROVA1</td><td valign=bottom>KO</td><td align=center valign=bottom>2</td><td align=right valign=bottom>07 15</td><td valign=bottom>PROVA</td><td valign=bottom>OK</td><td align=center valign=bottom>3</td><td align=right valign=bottom>15 02</td><td valign=bottom>PROVA2</td><td valign=bottom>KO</td><tr><td align=center valign=bottom>4</td><td align=right valign=bottom>

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vlookup (2000)

    I m sorry but i have insert a little modify in the my last post

  4. #4
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vlookup (2000)

    ok! But i have many difficult to transalte in italian the formulas do you like to attache a simple sheet?

  5. #5
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: vlookup (2000)

    Please attach an example

  6. #6
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: vlookup (2000)

    The translations into Italian are:
    <table border=1><td valign=bottom>English</td><td valign=bottom>Italian</td><td valign=bottom>IF</td><td valign=bottom>SE</td><td valign=bottom>AND</td><td valign=bottom>E</td><td valign=bottom>ISNUMBER</td><td valign=bottom>VAL.NUMERO</td><td valign=bottom>VALUE</td><td valign=bottom>VALORE</td><td valign=bottom>LEFT</td><td valign=bottom>SINISTRA</td></table>
    You can get an add-in for translations from here

    The formula becomes (if I got my translations right)
    =SE(E(VAL.NUMERO(VALORE(SINISTRA(A1,2))),SINISTRA( B1,5)="PROVA"),"OK","KO")

  7. #7
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: vlookup (2000)

    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center valign=bottom>1</td><td align=right valign=bottom>08 03</td><td valign=bottom>PROVA1</td><td valign=bottom>OK</td><td align=center valign=bottom>2</td><td valign=bottom>AAAA</td><td valign=bottom>PROVA</td><td valign=bottom>KO</td><td align=center valign=bottom>3</td><td align=right valign=bottom>15 02</td><td valign=bottom>PROVA2</td><td valign=bottom>OK</td><tr><td align=center valign=bottom>4</td><td valign=bottom>

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vlookup (2000)

    Please go to this website:

    http://www.acoustics-noise.com/ATPtranslator.shtml

    and download the ATP function translator. It will enable you to copy and paste functions from here into it and translate them.
    This function:

    =IF(AND(ISNUMBER(VALUE(LEFT(A1,2))),B1="PROVA"),"O K","KO")

    translates to:

    =SE(E(VAL.NUMERO(VALORE(SINISTRA(A1;2)));B1="PROVA ");"OK";"KO")
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vlookup (2000)

    i m sorry, tks for all help. Still sorry :-)

  10. #10
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: vlookup (2000)

    Felice di aiutare. Congratulazioni sul transformarsi in uno starlounger. (Non parlo Italien cos

  11. #11
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vlookup (2000)

    If you see my messages, my english is TERRIBLE!!!!! But in the GOD community the important is the help from FRIENDS.
    I leave in NAPOLI do you like a cup of coffe? If you come to my city i have very happy to invite to drink an original coffe:-)

  12. #12
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vlookup (2000)

    Little modify:
    adding olso if A1=B1 insert OK and adding olso if the string finded in the vlookup is not present (#n/d) insert a blank.

  13. #13
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: vlookup (2000)

    Hi Salvatore

    Can you attach an example spreadsheet that lists possible inputs in columns A & B with the result you want in column C. This way we can ensure that a more general formula is created that suits your need.

    (In Italian)
    Potete fissare un foglio elettronico di esempio che gli input possibili delle liste in colonne A & B con il risultato voi desiderano in colonna C. Questo senso possiamo accertarsi che una formula pi

  14. #14
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vlookup (2000)

    HERE IS IT

    HAVE YOU READ Post: 356201

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: vlookup (2000)

    Is this what you are after?
    <pre>=IF(OR(A1=B1,AND(ISNUMBER(VALUE(LEFT(A1,2))), B1="PRESENTE")),"OK","KO")</pre>


    The question in your spreadsheet seems different than the ones in the posts.

    Steve

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •