Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    If statement and Vlookup (Excel 2000)

    I need to write an If Statement that if true will look up data in a vlookup table, if false - do nothing. The problem is I need to lookup B2 and G2. This is the way it should go: In cell N2 I need to write something like this. If M2 is blank then Vlookup( B2 and G2, $Y$2:$AD$532,6) Can you help me correct the formula for N2?

  2. #2
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If statement and Vlookup (Excel 2000)

    Cindy,
    I do not understand what you are trying to accomplish.
    What is in cells B2 and G2? Do you want to do the same thing if either of these is found in the VLookup?
    You may want to attach a sample workbook.

    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If statement and Vlookup (Excel 2000)

    B2 is the customer name and G2 is one of the customer's codes. (They can have several codes) In the Vlookup table I need the equation to match the customer name and the specific code in G2 to lookup the correct fee. Does this help. Seems to me an IF statement is needed to make sure M2 is blank. If it is then a match needs to be done for the customer name (B2) and the specifice code (G2) in order to retrieve the proper fee.

  4. #4
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If statement and Vlookup (Excel 2000)

    =IF(M2="",VLOOKUP(B2&G2,$Y$2:$AD$532,6),"")

    This assumes that column Y has the name and customer number concatenated together.

  5. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If statement and Vlookup (Excel 2000)

    Your equation did not work because the customer name and code are not concatenated. Since many customers can have the same code I need to have the customer name match and the customer code match. If both customer name and code match, then look up the right fee. I can send a sample if you need.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: If statement and Vlookup (Excel 2000)

    How is your lookup table setup?
    If you have names in 1 column and the codes in a row, you could use MATCH to lookup each and then combine them using an INDEX to get the intersection of the row and column.

    If they are setup differently, you will have to be more specific about the setup. (though to simplify the formula the setup I describe above might be the way to go).

    Steve

  7. #7
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If statement and Vlookup (Excel 2000)

    Yes, I think this is the way to go. Thank you.

  8. #8
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If statement and Vlookup (Excel 2000)

    A sample would be helpful.

    For the moment, I will assume that your names are in column Y and the codes are in column Z. You could add a column on the left border of your table (i.e. column X)that concatenates the name and code and then use the formula I had above, but with the table range extended to include column X.

    Steve's method would certainly work well, although with >500 rows in your current table, it will be a bit tedious to fill your new table manually. Creating the extra column I described would help you fill out your new table as well.

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: If statement and Vlookup (Excel 2000)

    You could create the table with formulas relatively easily, all you would need is the unique names in a column and the unique codes in the rows and the formula. You could then create the concatenated column as you suggest and vlookup the name and code from the left column and top row to get the values to fill the table: just 1 formula to fill the entire table.

    Once completed the formulas can be "paste special valued" and the original table deleted.

    Steve

  10. #10
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If statement and Vlookup (Excel 2000)

    Once you add the concatenated column to the original table, I'm not sure you need Steve's suggested table since you can do the VLOOKUP using the formula I suggested a few posts ago. I would agree that Steve's table setup is much better for human eyes to read and review for possible errors since patterns would be much easier to see in the 2-dimensional table.

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: If statement and Vlookup (Excel 2000)

    I agree. Adding the combined columns eliminates the need to the 2 way lookup table.

    I, personally, would prefer the 2 way table and you mention very good reasons why it is easier to update and use.

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •