+ Reply to Thread
Results 1 to 6 of 6

formula help to remove #N/A

  1. #1
    Forum Contributor
    Join Date
    05-03-2004
    Location
    England
    MS-Off Ver
    2003 Excel
    Posts
    118

    formula help to remove #N/A

    Hi all,
    I am using the following formula to look up a value when validated data "yes"or "no" is in A1.

    =IF(A1="yes",vlookup(b1:c10,3),vlookup(b1:c10,4)

    this works ok if "yes" or "no" is in A1.

    how can I stop it giving #N/A if neither is selected in A1.

    Thank you in advance.

    Greg.

  2. #2
    Forum Expert swatsp0p's Avatar
    Join Date
    10-07-2004
    Location
    Kentucky, USA
    MS-Off Ver
    Excel 2010
    Posts
    1,545
    I'm sorry, Greg, but your formula, as presented, won't work at all. Your VLOOKUP statement is incomplete, not enough arguments. What value are you looking up? What is your lookup range? What column are you seeking to return a value from?

    You say your formula works if A1 is either Yes or No. I wonder how?

    More info, please
    Bruce
    The older I get, the better I used to be.
    USA

  3. #3
    Forum Contributor
    Join Date
    05-03-2004
    Location
    England
    MS-Off Ver
    2003 Excel
    Posts
    118
    Hi,

    many apologies I tried to simplify my complicated formula so it would be easier to understand and got it completely wrong. The formula should have read,

    =IF(A1="yes",VLOOKUP(B1,C1:E5,2),VLOOKUP(B1,C1:E5,3))

    sorry for the mistake.

    How do I make it not return #N/A if B1 is empty.

    Thanks,

    Greg.

  4. #4
    Forum Expert swatsp0p's Avatar
    Join Date
    10-07-2004
    Location
    Kentucky, USA
    MS-Off Ver
    Excel 2010
    Posts
    1,545
    Try this:

    =IF(B1="","",IF(A1="yes",VLOOKUP(B1,C1:E5,2),VLOOKUP(B1,C1:E5,3))) will return a blank cell if B1 is blank. However, #N/A will be returned if an exact match is not found

    or

    =IF(ISERROR(IF(A1="yes",VLOOKUP(B1,C1:E5,2),VLOOKUP(B1,C1:E5,3))),"",IF(A1="yes",VLOOKUP(B1,C1:E5,2),VLOOKUP(B1,C1:E5,3)))

    Will return a blank for any error.

    Your choice which to use.

    HTH

    Bruce

  5. #5
    Forum Contributor
    Join Date
    05-03-2004
    Location
    England
    MS-Off Ver
    2003 Excel
    Posts
    118
    Thank you Bruce, just what I needed,

    Greg.

  6. #6
    Forum Expert swatsp0p's Avatar
    Join Date
    10-07-2004
    Location
    Kentucky, USA
    MS-Off Ver
    Excel 2010
    Posts
    1,545
    Greg: You are most welcome. Glad I was able to help. Thanks for the feedback.

    Cheers!

    Bruce

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1