# Thread: Two lists (excel 2000)

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

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