Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 14 of 14

Thread: VLOOKUP formula

  1. #11
    Board Regular
    Join Date
    Mar 2002
    Location
    Kent, the Garden of England, that no one has bothered to weed.
    Posts
    65
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aladin

    Sorry about this -

    both the results for the =ISNUMBER(XX) formulas was TRUE.

    I typed the formula into the wrong sheet - DOH!

    Sorry
    Simon

  2. #12
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,045
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    On 2002-04-30 03:47, MrStressed wrote:
    Aladin

    =ISNUMBER(B3) - my result - TRUE

    Cheers
    Simon
    What we have here a data type mismatch that also leads to #N/A's when unexpected.

    Go to the front sheet.
    Copy an empty, unused cell.
    Select all of the lookup values in B from B11 on.
    Activate the option Edit|Paste Special >Add.

    Now test again:

    =ISNUMBER(B11)

    which should return TRUE.

    When you're thru with this, I'll propose a layout for your front sheet to compute the desired sums/totals for each of your lookup values.

  3. #13
    Board Regular
    Join Date
    Mar 2002
    Location
    Kent, the Garden of England, that no one has bothered to weed.
    Posts
    65
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aladin

    My error on the =ISNUMBER(XX) formula. Both results were TRUE.

    I'm ready - hit me!

    Cheers
    Simon

  4. #14
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,045
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default


    In your front sheet:

    In C11 enter:

    =SUMIF(D11:O11,"<>#N/A")

    Double click on the fill handle (the little black square) in the lower right corner of C11.

    In D11 enter and double click on its fill handle:

    =VLOOKUP($B11,midsSALCOM!B$3:$V$905,H$2,0)

    In E11 enter and double click on its fill handle:

    VLOOKUP($B11,birmSALCOM!B$3:$V$912,H$2,0)

    In F11 enter and double click on its fill handle:

    =VLOOKUP($B11,avctSALCOM!B$3:$V$900,H$2,0)

    Follow up the above logic until you have all of your 12 lookup tables.

    The above is more efficient than trying to avoid #N/A's in a single mega-formula.

    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
  •