Similar once again to blank formula fields
Eliminate Pivot Table Annoyances
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Similar once again to blank formula fields

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Melbourne, Victoria
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    e.g. A1 - data entry, B1 - VLOOKUP function, C1 - VLOOKUP function, where B1 and C1 look up A1.

    When A1 is blank, B1 and C1 display #VALUE (or something to that effect). When A1 is blank I want them to be blank.

    I put in an IF statement - IF(,(VLOOKUP(A1,'Table!A9:B20',2,0),"")

    What do I put in as the first variable of the IF statement. Or are there some other ideas?

    Please help!

  2. #2
    Board Regular Steve Hartman's Avatar
    Join Date
    Feb 2002
    Location
    Houston,Texas
    Posts
    417
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    What do I put in as the first variable of the IF statement. Or are there some other ideas?
    A1<>""

  3. #3
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Danielle:
    If your lookup value is in cell A1, your lookup range is A9:B20, you are looking up the value in column 2, you want an exact match ... and if A1 is blank you want the looked up value (say in cell B1) to be also blank, then your formula in cell B1 should be:

    =if(A1="","",vlookup(A1,$A9:$B20,2,0))

    I hope this is what you want ... please post if it works for you, otherwise explain a little further what you are trying to acomplish!
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  4. #4
    New Member
    Join Date
    Mar 2002
    Location
    Melbourne, Victoria
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The above doesn't work. Can anyone else help?

    The problem is explained in the first post but if you've missed it:

    A1 - Normal data, B1 - VLOOKUP function, C1 - VLOOKUP function, where B1 & C1 look up A1.

    When A1 is blank, B1 and C1 show #VALUE. What I want is an IF statement so that when A1 is blank, so are B1 and C1.

    Can anyone help?

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Ireland
    Posts
    133
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yogi answered your query above. That should work. What you need to do is put =if(a1="","",VLOOKUP(A1,'Table!A9:B20',2,0)) in cell B1 & C1. This way if A1 is blank, it doesn't even look at the VLOOKUP function. I hope this works... be sure to let us know!


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

    Default

    On 2002-03-19 05:12, danielle wrote:
    The above doesn't work. Can anyone else help?

    The problem is explained in the first post but if you've missed it:

    A1 - Normal data, B1 - VLOOKUP function, C1 - VLOOKUP function, where B1 & C1 look up A1.

    When A1 is blank, B1 and C1 show #VALUE. What I want is an IF statement so that when A1 is blank, so are B1 and C1.

    Can anyone help?
    What do you get if you try the following:

    =LEN(A1)

    while VLOOKUP ends up in a #VALUE! error?


  7. #7
    New Member
    Join Date
    Mar 2002
    Location
    Melbourne, Victoria
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    When I typed in what everyone suggested it didn't work so I tried to do it through the Function Wizard and for some weird reason it worked. This is the statement I ended up with.

    =IF(C21>"",VLOOKUP(C21,'Lookup Table'!A10:B24,2,0),"")

    Go figure!!

    Thanks for everyone's help

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

    Default

    On 2002-03-19 06:54, danielle wrote:
    When I typed in what everyone suggested it didn't work so I tried to do it through the Function Wizard and for some weird reason it worked. This is the statement I ended up with.

    =IF(C21>"",VLOOKUP(C21,'Lookup Table'!A10:B24,2,0),"")

    Go figure!!

    Thanks for everyone's help
    Somehow I don't like what that wizard proposes.

    The formula Anand suggested should work.

    By the way, I asked applying LEN to see whether you had a space instead of a blank in the cell from where you pick up the lookup value.

    Aladin

  9. #9
    New Member
    Join Date
    Mar 2002
    Location
    Melbourne, Victoria
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I didn't try LEN because I don't know what it is and by that time my IF statement worked.

    By function wizard I mean:
    Insert --> Function --> Logical, IF --> OK --> then I basically filled in the blanks. I don't understand why when I type in the exact same formula it doesn't work!

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

    Default

      
    On 2002-03-19 07:23, danielle wrote:
    I didn't try LEN because I don't know what it is and by that time my IF statement worked.

    By function wizard I mean:
    Insert --> Function --> Logical, IF --> OK --> then I basically filled in the blanks. I don't understand why when I type in the exact same formula it doesn't work!
    If the word CAR is in B1, =LEN(B1) in C1 will show 3. If B1 is blank, C1 will show 0.

    Aladin

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
  •  

 

 
DMCA.com