+ Reply to Thread
Results 1 to 3 of 3

Receiving #N/A as Result for a Formula

  1. #1
    Registered User
    Join Date
    01-27-2009
    Location
    Owen Sound, Ontario
    MS-Off Ver
    Excel 2016
    Posts
    71

    Receiving #N/A as Result for a Formula

    I am hoping someone can help me. I have a Array Lookup formula in a cell. The Fomula is:

    =LOOKUP(H2,F2:G32)

    The Array Table is:

    Budget
    Code Program
    1 Medical Officer of Health
    2 Manager Budget
    3 Child Health
    4 Dental
    6 Media
    7 Epidemiology
    9 Vaccine Preventable Disease
    11 Munn-Health Protection Disease
    12 Sexual Health
    13 Handley
    14 Manager (Hart)
    15 Health
    16 Health Protection
    17 Communicable Disease
    20 Team
    22 Safe Food Protection
    28 Health Promotion
    30 CD
    32 Cancer
    34 Mgr-Clerical Services -
    35 Administration -
    36 Clerical Support
    37 E.A. Management
    47 Small Water
    48 Prevention of Falls
    50 Tobacco-Basic/Policy Falls
    54 SFO-Bylaw & Falls
    58 Ontario Healthy Smiles
    66 Priority Populations
    70 Speech and Language
    76 Panorama

    The formula works when I type a number into the column just before the column with the formula in it, the corresponding progam name displays but until I type in the budged code number the #N/A message displays in the column I have the formula in.

    I do remember having this issue with a spreadheet several years ago and I know I found a solution with the help of someone on the forum but I must have lost what the solution was. I would like the cells to remain blank until I enter the corresponding budget number budget number

  2. #2
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Receiving #N/A as Result for a Formula

    Perhaps

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Receiving #N/A as Result for a Formula

    Using LOOKUP with your table, if you enter a code that doesn't exist, you will get an incorrect response.

    I suggest that you use VLOOKUP instead which will in this case tell you that the code doesn't exist if there isn't a matching code for the value in H2.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Issue with Complex formula only when now referencing different sheets. receiving #VALUE!
    By Shrad013 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-27-2013, 03:30 PM
  2. [SOLVED] Receiving #DIV/0! Error in Basic Formula
    By DDM64 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-10-2013, 11:29 AM
  3. Receiving #NAME? error when adding MONTHS to a formula
    By smsolomon in forum Excel General
    Replies: 2
    Last Post: 02-09-2012, 12:03 PM
  4. Receiving 0 when copying formula
    By mgtid in forum Excel General
    Replies: 2
    Last Post: 09-16-2007, 06:36 PM
  5. No errors, but not receiving desired result
    By moike in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-25-2006, 04:18 PM

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