1. ## Compare (2000)

I have 2 columns of text and data on a worksheet; the first column is text (names of locations), and column 2 are prices. On worksheet 2, I again have names in column 1, but not exactly the same names. For example, I may have 100 names on worksheet 1, and only 90 on worksheet 2, most, but not all may be on worksheet 1. How can I compare the two worksheets and determine which names are on worksheet 1 but not on worksheet 2, and include the respective prices for those not listed on worksheet 2? I would like to be able to do this for 12 worksheets, as each worksheet represents a month; hence, I would like to do a yearly summary.
Thanks,
Jeff

2. ## Re: Compare (2000)

Hi There

The attached may help. Sheet 1 contains a price list. Sheet 2 contains a lookup in column B, dependent upon value in column A, that looks for the value in Sheet 1 column A and if it finds it, retrieves the corresponding price from column B on Sheet 1. The command is show below:

=IF(A1<>"",IF(ISERROR(MATCH(A1,Sheet1!\$A\$1:\$A\$7,0) ),0,INDEX(Sheet1!\$B\$1:\$B\$7,MATCH(A1,Sheet1!\$A\$1:\$A \$7,0))),0)

The MATCH(A1,Sheet1!\$A\$1:\$A\$7,0) looks for the value in A1 in the list Sheet1!\$A\$1:\$A\$7, if found, this returns a number between 1 and 7 (in this scenario).

The INDEX(Sheet1!\$B\$1:\$B\$7, part of the command looks in column B in Sheet 1 and returns the Nth item from the list, where N is this the number returned by the MATCH statement above.

Extend the ranges to suit. You may be better creating a lookup table somewhere and then giving this a named range.

Hope this helps.

Regards
Peter

3. ## Re: Compare (2000)

Works great, Peter. BTW, any idea of a thread that explains how to construct a lookup table?
Thanks again.
Jeff

4. ## Re: Compare (2000)

Hi Jeff

Do a search is probably the best way to find related threads. In the meantime, I've updated the worksheet I sent you to use lists and lookups, this may give you some ideas of your won to work with. Good luck!

Regards
Peter

#### Posting Permissions

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