I did this on the same sheet, but here’s the concept…
“Name” in the first table is in cell A1
“Name” in the second table is in cell A6
The formula in B7 is: =IF(ISNA(HLOOKUP(B$6,$B2:$C2,1,FALSE)),0,1)
This can be copied across and down. Make sure you have the dollar signs correct. It assume the names in the two tables are in the same order.
If you can’t make that assumption, you can use the MATCH and INDEX functions to do lookups in both directions. How to do this is a bit more complicated than I can fit into a messageboard post, though …
This will display 1 if the state is in list 1, 2 if in list 2 or 0 if no match.
If you want it 1 and zero, then set all the values in column D of sheet 1 to be 1.
e.g.
CA AZ MI NY OH
Tom 1 2 0 0 0
Dick 0 0 1 2 0
Harry 1 0 0 0 2
If you don’t have Excel 2007 you can get the 1-0 result using the
=IF(ISNA(VLOOKUP(TRIM($A2)&TRIM(B$1),$F$4:$G$9,2,FALSE)),0,1)
formula much as indicated by k364, but in this case the names/states can be in any sequence.
If the data was arranged as below a pivot table would be, by far, the easiest and most elegant solution. A couple of clicks and I had the desired answer.
Pivot tables are the most powerful, but not often used features of Excel.
Name Listing
Tom CA
Dick MI
Harry CA
Tom AZ
Dick NY
Harry OH
Assuming that you start on A1 on each tab and that names are same order:
=IF(COUNTIF(Sheet1!$B2:$C2,B$1)>0,1,0)
If you want to put names in any order you want on either page, you’ll need to create a dummy variable in the 4th column on sheet 1 (next to Listing 2) and just number the cells 1,2,3,4 (going down - so 1 across from the first name, 2 across the second, etc.)