Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 25

Thread: VLOOKUP

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

    Default

    I'm using VLOOKUP to return a unit price for a model code to sheet JOB01 from a price list on sheet Price. The formula

    =IF(A5>1,VLOOKUP(A5,Price!$A$2:$B$223,2,Price!$B$2:$B$223),"")

    doesn't fully work.

    The models (in column A)change (sequentially increment) alphanumerically i.e., they begin B15, B18, etc. through B96 then shift to BB15, BB18, etc. and on and on through several alpha combinations. The formula will work through the B15 - B96 range but once you get into the BB's (or beyond) the price (from Col B) that is returned is always the price before the one you are seeking, i.e. if you enter BB24 you'll get the price for BB21. What's wrong with the formula? (Please write a working formular.)

    Also if no value preceeds the formula, i.e. A5 is blank, the formula cell is #N/A - how can I get rid of it.

    Thanks,

    GDawg

  2. #2
    Board Regular zacemmel's Avatar
    Join Date
    Apr 2002
    Location
    Redmond, WA
    Posts
    636
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    =IF((A5>1)*(ISERROR(VLOOKUP(A5,Price!$A$2:$B$223,2,0))<>1),VLOOKUP(A5,Price!$A$2:$B$223,2,0),"")

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Southfield,MI USA
    Posts
    2,312
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    G'day,

    =IF(A5>1,VLOOKUP(A5,Price!$A$2:$B$223,2,Price!$B$2:$B$223),"")
    How about:

    =IF(OR(A5<=1,COUNTIF($A$2:$A$223,A5)),"",VLOOKUP(A5,Price!$A$2:$B$223,2,0))

    Adam



  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,496
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default



    I do not have your data; consequently, I did not test the following.

    =IF(AND(LEN(A5),COUNTIF(Price!$A$2:$B$223,A5)),VLOOKUP(A5,Price!$A$2:$B$223,2,FALSE),"")

    If A5 and A5 not found show blank
    find A5 in range
    show data from column 2
    exact match


    Please advise if this works or provide
    feedback.

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

    Default

    Thanks Dave but... interestingly it returns data for the first series of B's but won't return any values for numbers in the BB and beyond. On my original formula I always got a return but after the single B's it would be one value behind.

    Here's a slice of the unit numbers and correspondening prices:

    B72 230.00
    B78 250.00
    B84 264.00
    B90 266.00
    B96 288.00
    BB36 120.00
    BB39 122.00
    BB42 124.00
    BB45 134.00
    BB48 142.00
    DB21 142.00
    DB24 152.00
    DB27 158.00
    DB30 164.00
    RB30 114.00
    RB33 120.00
    RB36 124.00
    RB39 132.00
    RB42 138.00

  6. #6
    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 11:16, GDawg wrote:
    I'm using VLOOKUP to return a unit price for a model code to sheet JOB01 from a price list on sheet Price. The formula

    =IF(A5>1,VLOOKUP(A5,Price!$A$2:$B$223,2,Price!$B$2:$B$223),"")

    doesn't fully work.

    The models (in column A)change (sequentially increment) alphanumerically i.e., they begin B15, B18, etc. through B96 then shift to BB15, BB18, etc. and on and on through several alpha combinations. The formula will work through the B15 - B96 range but once you get into the BB's (or beyond) the price (from Col B) that is returned is always the price before the one you are seeking, i.e. if you enter BB24 you'll get the price for BB21. What's wrong with the formula? (Please write a working formular.)

    Also if no value preceeds the formula, i.e. A5 is blank, the formula cell is #N/A - how can I get rid of it.

    Thanks,

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

    I believe even

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

    would suffice.

    Aladin


  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,496
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default



    The formula works OK for me.

    Check for extra spaces.

    You can compare the len of say BB36 at
    both locations or

    copy the lookup value BB36 to the LookupTable and see if you get proper result.

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

    Default

    Aladin - same as with Dave.

    Either formula will work in the first series of B values, but once you get to the BB's and up nothing is retuned.

    Thanks,

    GDawg

  9. #9
    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 11:56, GDawg wrote:
    Aladin - same as with Dave.

    Either formula will work in the first series of B values, but once you get to the BB's and up nothing is retuned.

    Thanks,

    GDawg
    As Dave noted, check for extraneous spaces both around the lookup values as well as the values in the first column of the lookup table. You can check this with LEN (Note however that the LEN test is not needed in the VLOOKUP formula itself).

    Aladin

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

    Default

    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

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
  •