Compare/search Excel Sheet

I have to Excel tables. They are sorted after one attribute. Now I want to compare these tables. The problem is, my tables don't have the same values.

e.g. Table 1 1 2 3 4 5 7 9

table 2 1 3 4 5 6 7 9

if I try to compare these fields hardc开发者_JAVA百科oded (IF(A1=B1;x;y)) i have some missing rows as soon as I get to 3 in Table2. So how am I able to search the table for this number and compare its corresponding row to the other? Please no VB-skript ;)

Thanks for your help

In both tables you can use a =VLOOKUP() to the other table to check for existance. If you are working with ranges that frequently change in size, you would create names.

Example:

• Workbook/Sheet1 contains values in A1 .. A5 .... you name this "MyData"
• Workbook/Sheet2 contains values in A1 .. A9 .... you name this "MyData"
• add into Sheet1.[B1] the formula =VLOOKUP(A1,Book2.xls!MyData,1,FALSE) and copy down
• add into Sheet2.[B1] the formula =VLOOKUP(A1,Book1.xls!MyData,1,FALSE) and copy down

in both books a #N/A in column B indicates a key that is not existing in the other file

You can use the =LOOKUP(lookup_value,lookup_vector,result_vector) e.g in Sheet1 to retrieve a value from column result_vector (from Sheet2) where the value in lookup_vector (from Sheet2) matches the lookup_value (in Sheet1).

example in Sheet1 (looking at Sheet2-> Range [MyThirdColumn]: =LOOKUP(A1;Book2.xls!MyData;Book2.xls!MyThirdColumn)

In combination with the above =VLOOKUP(....) which determines if the key is found at all, you can retrieve (and further compare) attached values: =if(NOT(ISNA(VLOOKUP(....))),LOOKUP(....),"nope!")

Hope that helps you develop your formula

Here are a few tips for you depending on the version of Excel you are using :

• http://www.mrexcel.com/articles/duplicates-with-conditional-formatting.php
• http://office.microsoft.com/en-us/excel-help/delete-duplicate-rows-from-a-list-in-excel-HA001034626.aspx