Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Dec 2004
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Two lists (excel 2000)

    Hello there? I wish a code/vba that could compare two lists that return a value from the first list, if both dates are matched.
    By the attached file, for example, E2 would be 4000.
    Ok, I found a solution with a vlookup and If function in cells E2:E14, but it has two problems:
    1 - not matches, returns errors values;
    2 - the code would be inefficient, since it compares each cell in the range E2:E14 with A2:A4 (a smaler database)

    I am thankful for any tip [img]/forums/images/smilies/smile.gif[/img]!

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Two lists (excel 2000)

    You say that you would like code as the Vlookup and If functions return an error on no matches. Did you nest an ISERROR function into that? This will sort out the problem of errors displaying!
    Try ... =IF(ISERROR(VLOOKUP(D2,A2:B4,2,FALSE)),"",VLOOKUP( D2,A2:B4,2,FALSE))
    Regards,
    Rudi

  3. #3
    New Lounger
    Join Date
    Dec 2004
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Two lists (excel 2000)

    Thanks for your reply! But the problem with this solution is that in fact my "list2" is about 400 entries and "list1" is about 150 entries. My intention is to create a VBA code, and I think it would be better to return just 150 values instead of 400.
    Anyway I will try your solution. Thanks!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Two lists (excel 2000)

    The advantage of a formula is that the result will be updated automatically. If you use VBA, you would have to run the code each time the data change.

    You could use a single array formula in the entire range in column E. In the example workbook, the formula in E2:E14 would be:

    =IF(ISERROR(VLOOKUP(D214,A2:B4,2,FALSE)),"",VLOOKUP(D214,A2:B4,2,FALSE))

    This formula must be confirmed with Ctrl+Shift+Enter instead of just Enter. Since it is a single formula, it is more efficient than having 13 individual formulas in E2, E3, ..., E14.

    See attached workbook.

Posting Permissions

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