Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 25

Thread: VLOOKUP

  1. #11
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,038
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    On 2002-05-07 12:42, GDawg wrote:
    Aladin

    By extraneous spaces do you mean blank cells or cells that have value not relative to the table? There are none.

    Many thanks,

    GDawg
    Take BB36 as example housed in Q1. If

    =LEN(Q1)=4 does not hold, it's pretty sure that

    =LEN(TRIM(Q1)) will result in 4.

    if the above scenario obtains, we'd say the entry in Q1 had unintended space(s) (whose ASCII code 32) in front and/or at the back.

    Aladin

  2. #12
    New Member
    Join Date
    Apr 2002
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    That worked!!! There were extra spaces in a majority of the cells. Many, many thanks. One more question though - if the exact number say BB37 doesn't exist is there a way to have it return the next higher value, i.e., the value for a BB39?

    Thanks,

    GDawg

  3. #13
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,038
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    On 2002-05-07 13:22, GDawg wrote:
    That worked!!! There were extra spaces in a majority of the cells. Many, many thanks. One more question though - if the exact number say BB37 doesn't exist is there a way to have it return the next higher value, i.e., the value for a BB39?

    Thanks,

    GDawg
    Array-enter:

    =IF(COUNTIF(Price!$A$2:$A$223,A5),VLOOKUP(A5,Price!$A$2:$B$223,2,0),INDEX(Price!$B$2:$B$223,MIN(IF((LEN(Price!$A$2:$A$22 3)=LEN(A5))*(RIGHT(Price!$A$2:$A$223,2)>=RIGHT(A5,2)),(ROW(Price!$A$2:$A$223))))))

    To array-enter a formula, you need to hit control+shift+enter at the same time, not just enter.

    Not sure whether this is what you're looking for.

    Keep also in mind that this is an expensive formula.

    Aladin

    [ This Message was edited by: Aladin Akyurek on 2002-05-08 05:51 ]

  4. #14
    New Member
    Join Date
    Apr 2002
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aladin

    You're right - that formula is too expensive. Would it be simpler to return a message that read INVALID NO. or something like that?

    Thanks,

    GDawg

  5. #15
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,038
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    On 2002-05-08 05:11, GDawg wrote:
    Aladin

    You're right - that formula is too expensive. Would it be simpler to return a message that read INVALID NO. or something like that?

    Thanks,

    GDawg
    =IF(COUNTIF(Price!$A$2:$A$223,A5),VLOOKUP(A5,Price!$A$2:$B$223,2,0),"Not Available")

    should be OK no?

    BTW, does the array-formula return the results as desired? If so, we can tradeoff space (memory) against time (speed) and see whether that's acceptable.

    Aladin

  6. #16
    New Member
    Join Date
    Apr 2002
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aladin,

    The "Not Available" formula worked fine. As for as the monster one I couldn't get it to work. First Excel claimed it had "(" problems and if I let Excel change the formula it would return some value but never the right one.

    I need to learn more of the rudiments of Excel formulas - do your recommend a good book!!

    Thanks,

    GDawg

  7. #17
    Board Regular sen_edp's Avatar
    Join Date
    Mar 2002
    Location
    Hellas
    Posts
    555
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    HELLO THERE

    I think it's time for Aladin to write a book about excel formulas.It will be a top one
    Till then try John's Walkenbach book "excel 2000 formulas"

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

    -----------------------------------------------------------------------------------------------------------------------------------------
    ANDREAS ( WINDOWS 7 PRO, MICROSOFT EXCEL 2010)

  8. #18
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,038
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    On 2002-05-08 05:43, GDawg wrote:
    Aladin,

    The "Not Available" formula worked fine. As for as the monster one I couldn't get it to work. First Excel claimed it had "(" problems and if I let Excel change the formula it would return some value but never the right one.

    I need to learn more of the rudiments of Excel formulas - do your recommend a good book!!

    Thanks,

    GDawg
    The monster needs a closing paren, an omission due to glueing to formulas into by Copy and Paste. It works but the question is Does it work the intended way? You can get a copy of that if you want to.

    Aladin

  9. #19
    New Member
    Join Date
    Apr 2002
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aladin,

    No, the monster never worked the intended way at least for me.

    I appreciate the help from you and the others that contributed.

    Keep up the great work.

    Ciao,

    GDawg

  10. #20
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,038
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    On 2002-05-08 06:36, GDawg wrote:
    Aladin,

    No, the monster never worked the intended way at least for me.

    I appreciate the help from you and the others that contributed.

    Keep up the great work.

    Ciao,

    GDawg
    Ciao... but not yet...

    If the lookup value is BB36, given the sample I get 120 as result.

    If the lookup value is BB43 (which does not exist lterally in the sample), I get 142 as result.

    Interested?

    Aladin

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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