Adding vlookup formulas
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Adding vlookup formulas

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I'm sure that this problem has already been addressed, however, I could not find a post answering my question. Could someone please reply to this response with a link to the corresponding post. Thank you.

    If the question has not already been asked the following are my formulas:

    =(IF(ISNA(VLOOKUP($B88,'exec data'!$A$5:$O$51,6,FALSE)),0,VLOOKUP($B88,'exec data'!$A$5:$O$51,6,FALSE)))

    +

    (IF(ISNA(VLOOKUP($B88,'10 Reorg Data'!$A$5:$O$51,6,FALSE)),0,VLOOKUP($B88,'10 Reorg Data'!$A$5:$O$51,6,FALSE)))

    Both equations work alone, however, I would like the re****s of both "summed" in the same cell. (i.e. the first equation results in 1 and the second equation results in 1, therefore 2 should be the answer 1+1=2)

    Thank you for your time.

    -Dave

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

    Default

    On 2002-04-02 08:03, croweld89 wrote:
    I'm sure that this problem has already been addressed, however, I could not find a post answering my question. Could someone please reply to this response with a link to the corresponding post. Thank you.

    If the question has not already been asked the following are my formulas:

    =(IF(ISNA(VLOOKUP($B88,'exec data'!$A$5:$O$51,6,FALSE)),0,VLOOKUP($B88,'exec data'!$A$5:$O$51,6,FALSE)))

    +

    (IF(ISNA(VLOOKUP($B88,'10 Reorg Data'!$A$5:$O$51,6,FALSE)),0,VLOOKUP($B88,'10 Reorg Data'!$A$5:$O$51,6,FALSE)))

    Both equations work alone, however, I would like the re****s of both "summed" in the same cell. (i.e. the first equation results in 1 and the second equation results in 1, therefore 2 should be the answer 1+1=2)

    Thank you for your time.

    -Dave
    Simply enter them as

    =Formula1+Formula2

    in one cell.

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I just tested it and it worked fine for me....what result are you getting?


  4. #4
    New Member
    Join Date
    Mar 2002
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I followed the logic on my formulas and found that my new spreadsheet (2nd sheet) was skewed. Simply adding the formulas did work.

    Do you have any suggestions on how to simplify these formulas?

    Thanks for your time.

    -Dave

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    There is a way to simplify your need to do the double lookup by using a countif statement against the first column of your critia. But for simplicity, I would just do your vlookups in two cells, say G1 and G2, and then add them with the formula
    =SUMIF(G1:G2,"<>#n/a")

    good luck

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

    Default

      
    On 2002-04-02 08:42, croweld89 wrote:
    I followed the logic on my formulas and found that my new spreadsheet (2nd sheet) was skewed. Simply adding the formulas did work.

    Do you have any suggestions on how to simplify these formulas?

    Thanks for your time.

    -Dave
    You could rewrite

    =(IF(ISNA(VLOOKUP($B88,'exec data'!$A$5:$O$51,6,FALSE)),0,VLOOKUP($B88,'exec data'!$A$5:$O$51,6,FALSE)))

    as

    =IF(COUNTIF('exec data'!$A$5:$A$51,$B88),VLOOKUP($B88,'exec data'!$A$5:$O$51,6,0),0)

    Further you could give names to relevant ranges. For example,

    select the range $A$5:$A$51 in exec data, go the Name Box on the Formula Bar, type an intelligible name e.g., execLVALUES, and hit enter [ LVALUES from lookup values ].

    Now you can use this name in the COUNTIF instead of longish 'exec data'!$A$5:$A$51.

    Name the range 'exec data'!$A$5:$O$51, e.g., execLTABLE and use this name in VLOOKUP.

    Hope this helps.

    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