Comparing columns of data in Numbers '09

Both "spreadsheets" need to be within one document. Those are your two tables (using Numbers terminology). Don't make one table out of them; make them separate tables on a single sheet in one Numbers document. Your inventory table I'll call Table 1. Your obsolete parts I'll call Table 2. I'll assume the part numbers are in column A.

In another column of Table 1 (column B for instance) use the following formula:
=IF(ISERROR(VLOOKUP(A,Table 2 :: A,1,0)),"", "OBSOLETE")

Put it in the first row of column B. Modify as necessary if the numbers are not in column A or the table name is different or if the two tables are on different sheets. Drag/fill, fill-down, or copy/paste it to the rest of the rows. Column B will say "obsolete" if the part number matches one on the obsolete list.

Thanks Badunit - that helped.
I appreciate that it's not possible to create a formula in a cell which references itself but is it possible, having found the 'obsolete' cell answer to then change column A in Table 1?
I'm comparing members names and if they appear on the obsolete list I want to change the Status to Expired. But if they're not on the obsolete list I don't want to change the content of the text column Status. I couldn't find how to put two formulae together, the second based on the result of the first.
Thanks.

Message was edited by: 2Much2Do

You cannot change column A because it is not a formula.

I am not sure what you you are asking in your recent post. You asked about changing column A in Table 1 but I didn't see where you said what you wanted to change it to. If you want the part number in column A to be replaced with "Expired", the method below will work.

Table 1 = the master parts list (no formulas need to be in this table)
Table 2 = obsolete parts list (same as before)
Table 3 = a new table for displaying what you want to see. Make it as longer than your Table 1 if you plan to expand Table 1 at any time in the future.

Table 3 cell A2 =IF(ISERROR(VLOOKUP(Table 1 :: A,Table 2 :: A,1,0)),Table 1 :: A, "EXPIRED")

You may want to conditional format the cells in this column so a cell with a zero in it is white text (i.e., invisible text). This way the extra rows at the bottom will be blank, not zeros.

If I misunderstood and what you want is to change a different column (the status) then you can do basically the same thing. If the status is in Table 1 column C

Table 3 column C =IF(ISERROR(VLOOKUP(Table 1 :: A,Table 2 :: A,1,0)),Table 1 :: C, "EXPIRED")

EDIT: An alternate formula for the first one is

Table 3 cell A2 =IF(ISERROR(Table 1::A2),"",IF(ISERROR(VLOOKUP(Table 1 :: A2,Table 2 :: A,1,0)),Table 1 :: A2, "Expired"))

Copy/paste this formula to the whole column (I assumed row 1 was a header row). Now you won't have to conditional format the zeros. I was not comfortable with the zeros; I expected them to be error triangles. Because they were not error triangles, I was wondering where the zeros were coming from. It felt wrong.

Message was edited by: Badunit