vlookup & if statement
Prep for a pre-employment Excel test with Job Test Prep
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: vlookup & if statement

  1. #1

    Join Date
    Feb 2003
    Posts
    217
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default vlookup & if statement

     
    I am trying to perform a vlookup function & an if statement in the same cell. if a cell from spreadsheet 1 is found on spreadsheet 2 then check on the same row if cell j2 = l2. if cell j2 = l2, if true then check and see if j2 is > k2 by more than 10, change the color of cell in column w to green.

  2. #2
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi txksa:

    A little clearer description would help in understanding what you are trying to accomplish. Can you specify cell references you are comparing in Sheet1 and sheet2 -- in fact it will really help if you can post some sample data, showing what formula(s) you have used, what result you got, and what you think is the right result -- and then let us take it from there.
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  3. #3

    Join Date
    Feb 2003
    Posts
    217
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Problem is I am not sure how to write this properly.....
    First part:
    =vlookup(T2,Sheet1!G2:G1558,
    Second part:
    ex: if the vlookup shows that T2 matches Sheet1G8, then I want the formula to check if Sheet1 J8 = Sheet1 L8 (different column but same row as Sheet1 G8)
    Third part:
    ex: if sheet1 j8 = sheet1 L8 then I want to check and see if Sheet1 J8 > Sheet1 K8 by more than $10, I want the value of the box to return false and change the color of the cell to green

  4. #4
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Well, how about if we take it part by part. So, let us look at the first part --

    what are you trying to do with the VLOOKUP function? -- where T2 is the lookup value, your lookup range is Sheet1!G8:G558, then what? You see you have to specify that after matching T2 with data in the range G8:G558, determine which row the match occurs in , and then lookup the corresponding value in a particular column in the lookup range; since you have only one column in the lookup range, your Column number is 1; then you have to consider, whether you are looking for an exact match or to ensure that T2 lies within a range of values;

    anyway, you may or may not need VLOOKUP. It is better for you to describe in words, what are you trying to do in the first part.

    If the problem can be understood clearly, I am sure you will get meaningful help for what you are trying to do.
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  5. #5

    Join Date
    Feb 2003
    Posts
    217
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    sorry about that...for the first part....if cell T2 corresponds with a value in sheet1 column G, I want to formula to only check data for the second part on the corresponding row.

  6. #6
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Hi txksa:

    I would have prefered you build the solution part by part -- however, since you have provided a very specific and clear description, the single formula to do what you want is:

    =IF(INDEX($G$2:$L$1558,MATCH($T$2,$G$2:$G$1558,0),COLUMNS($G:J))=INDEX($G$2:$L$1558,MATCH($T$2,$G$2:$G$1558,0),COLUMNS($ G:L)),IF(INDEX($G$2:$L$1558,MATCH($T$2,$G$2:$G$1558,0),COLUMNS($G:J))-INDEX($G$2:$L$1558,MATCH($T$2,$G$2:$G$1558,0),COLUMNS($G:K))>10,FALSE,"SomeThingElse"))

    Please note I used the MATCH function in what you called part1 rather than using the VLOOKUP function that you were contemplating to use.

    Then since I am using cell O4 to record the value, for Conditional Formating of cell O4, I used ...

    Formula Is ... =O4=FALSE ... then FORMAT|Patterns -- green color

    see the following simulation where the formula has been applied ...
    ******** ******************** ************************************************************************>
    Microsoft Excel - y030423h1.xls___Running: xl97 : OS = Windows 98
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    G
    H
    I
    J
    K
    L
    M
    N
    O
    P
    Q
    R
    S
    T
    1
    2
    111111 7
    3
    211111
    4
    311111 FALSE
    5
    411111
    6
    71114314
    7
    611111
    8
    51114314
    9
    811111
    10
    911111
    Sheet1

    [HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    Is this what you are looking for?

    Please post back if it works for you now -- otherwise explain a little further and let us take it from there.
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

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