Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: VLOOKUP formula

  1. #1
    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

    All, sorry in advance for the length of this post.

    I have a sheet that i am trying to populate by using a VLOOKUP formula, searching for a numeric string in 12 sheets and populating the result of the search in my front sheet. After writing the formula I have got results where i should'nt have i.e. the numeric string has no related values (apart from 0) to populate as a total to the front sheet.
    Additionally I'm confused as to the term 'Range_lookup' which appears as the fourth section of the VLOOKUP formula wizard. what should I be using here ? my original range of numeric strings from my front sheet or ranges from the sheets of data I am looking at.

    Heres my formula at present..
    =VLOOKUP($B11,midsSALCOM!B$3:$V$905,H$2,$B11)+VLOOKUP($B11,birmSALCOM!B$3:$V$912,H$2,$B11)+VLOOKUP($B11,avctSALCOM!B$3:$ V$900,H$2,$B11)+VLOOKUP($B11,lseSALCOM!$B$3:$V$886,H$2,$B11)+VLOOKUP($B11,edSALCOM!$B$3:$V$936,H$2,$B11)+VLOOKUP($B11,gl SALCOM!$B$3:$V$918,H$2,$B11)+VLOOKUP($B11,udSALCOM!$B$3:$V$934,H$2,$B11)+VLOOKUP($B11,neSALCOM!$B$3:$V$913,H$2,$B11)+VLO OKUP($B11,nwSALCOM!$B$3:$V$914,H$2,$B11)+VLOOKUP($B11,wlSALCOM!$B$3:$V$908,H$2,$B11)+VLOOKUP($B11,ykSALCOM!$B$3:$V$904,H $2,$B11)+VLOOKUP($B11,clSALCOM!$B$3:$V$906,H$2,$B11)

    Any help you can offer would be great.

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Los Angeles, CA
    Posts
    752
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    OK, I was bit confused but here is what I do.

    Vlook(cell your looking up, range, column, true or false)

    Cell your looking up
    Range: highlight the range you'll be looking up and in the top left white box next to the formula bar. Write a name for the range, something small is good. Like "data2002"
    Column: the column number from where your range begins that has the info you want
    true or false: write one if these words, if you want an exact match false, if you want it round up true.

    I don't know if this answers your question, but maybe it can help simplify your formula and help you catch your error.

  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

    Hiya,

    I'm not sure how much this makes a difference but instead of a vlookup that looks like:

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

    Basically swap the last $B11 argument to a false-applied to each vlookup in the sum.

    Hope that helps,
    Adam

  4. #4
    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

    Adam

    I tried your solution but the formula returned '#N/A', even for cells that should have had a result.

    Can you think of anything else ?

    Cheers
    Simon

  5. #5
    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-29 08:56, MrStressed wrote:
    All, sorry in advance for the length of this post.

    I have a sheet that i am trying to populate by using a VLOOKUP formula, searching for a numeric string in 12 sheets and populating the result of the search in my front sheet. After writing the formula I have got results where i should'nt have i.e. the numeric string has no related values (apart from 0) to populate as a total to the front sheet.
    Additionally I'm confused as to the term 'Range_lookup' which appears as the fourth section of the VLOOKUP formula wizard. what should I be using here ? my original range of numeric strings from my front sheet or ranges from the sheets of data I am looking at.

    Heres my formula at present..
    =VLOOKUP($B11,midsSALCOM!B$3:$V$905,H$2,$B11)+VLOOKUP($B11,birmSALCOM!B$3:$V$912,H$2,$B11)+VLOOKUP($B11,avctSALCOM!B$3:$ V$900,H$2,$B11)+VLOOKUP($B11,lseSALCOM!$B$3:$V$886,H$2,$B11)+VLOOKUP($B11,edSALCOM!$B$3:$V$936,H$2,$B11)+VLOOKUP($B11,gl SALCOM!$B$3:$V$918,H$2,$B11)+VLOOKUP($B11,udSALCOM!$B$3:$V$934,H$2,$B11)+VLOOKUP($B11,neSALCOM!$B$3:$V$913,H$2,$B11)+VLO OKUP($B11,nwSALCOM!$B$3:$V$914,H$2,$B11)+VLOOKUP($B11,wlSALCOM!$B$3:$V$908,H$2,$B11)+VLOOKUP($B11,ykSALCOM!$B$3:$V$904,H $2,$B11)+VLOOKUP($B11,clSALCOM!$B$3:$V$906,H$2,$B11)

    Any help you can offer would be great.
    I assume that you have a number of lookup values in column B from B11 on in your front sheet. How many are they? And can you give some examples of these string values?

    And, what is the value of $H$2?

  6. #6
    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

    Hi Aladin

    I have several sheets that i'm trying to populate with this formula. The biggest sheet has about 270 'identifiers' that i am looking to return values to from the 'xxxSALCOM' sheets.

    examples of these identifiers are
    50166
    50038
    50040
    50072

    Cell 'H$2' contains the column reference for identifying the column to take data from on the 'xxxSALCOM' sheets, hopefully telling excel in which column to put the summed data.

    Hope this makes sense.

    Cheers
    Simon

  7. #7
    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

    I have several sheets that i'm trying to populate with this formula. The biggest sheet has about 270 'identifiers' that i am looking to return values to from the 'xxxSALCOM' sheets.

    Simon, the foregoing is a confusing statement. My understanding is that you lookup a value (e.g., 50166) from within your front sheet in each of the SALCOM sheets and add up the returned values. Is this right?

    examples of these identifiers are
    50166
    50038
    50040
    50072


    Fine. Would you please check the following:

    In your front sheet in an unused cell type:

    =ISNUMBER(B11)

    What do you get as result?

    Cell 'H$2' contains the column reference for identifying the column to take data from on the 'xxxSALCOM' sheets,

    Right. What is the value that H2 houses?

    hopefully telling excel in which column to put the summed data.

    This is again a confusing statement. My understanding is that H2 must house a number which indicates where to look in the range B$3:$V$900 to find a value associated with a lookup value.

    FYI, the VLOOKUP function has the following syntax:

    VLOOKUP(lookup-value,lookup-table,where-to-look-in-the-llokup-table,desired-match-type)

    where desired-match-type can be either approximate (indicated by 1 or TRUE) or exact (indicated by 0 or FALSE).

    Aladin

  8. #8
    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

    YOU SAID - My understanding is that you lookup a value (e.g., 50166) from within your front sheet in each of the SALCOM sheets and add up the returned values. Is this right? THAT IS CORRECT.

    =ISNUMBER(B11) my result - FALSE

    H2 value is 7 (B2=1, C2=2 etc) and is my 'where to look in the lookup table'

    sorry to be so confusing.

    Cheers
    Simon



  9. #9
    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:40, MrStressed wrote:
    Aladin

    YOU SAID - My understanding is that you lookup a value (e.g., 50166) from within your front sheet in each of the SALCOM sheets and add up the returned values. Is this right? THAT IS CORRECT.

    =ISNUMBER(B11) my result - FALSE

    H2 value is 7 (B2=1, C2=2 etc) and is my 'where to look in the lookup table'

    sorry to be so confusing.

    Cheers
    Simon
    One more thing:

    Go to the midsSALCOM sheet and type in an empty cell:

    =ISNUMER(B3)

    What result do you get?

    Aladin

  10. #10
    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

    =ISNUMBER(B3) - my result - TRUE

    Cheers
    Simon

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
  •