Sheet 1:
Name Listing 1 Listing 2
Tom CA AZ
Dick MI NY
Harry CA OH
Sheet 2:
Name CA AZ MI NY CA OH
Tom
Dick
Harry
How do I get, in Sheet 2, a “1” for every match of Name/Listing and a “0” for every non-match?
Problem is, dragging it to copy across cells screws up because of the absolute/relative thingie.
More specifics will be provided if needed, but this is the gist of it.
Thanks.
I think you want to look into using a pivot table. Alas, pivot tables and I don’t get along well, so I’ll leave the details to someone else.
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 …
Can be done with a simple enough vlookup if you transform sheet 1 as below;
Name Listing Concatenate List
Tom CA TomCA 1
Dick MI DickMI 1
Harry CA HarryCA 1
Tom AZ TomAZ 2
Dick NY DickNY 2
Harry OH HarryOH 2
Then in each cell within the grid on sheet 2 your formula (if you have Exel 2007) is:
=IFERROR(VLOOKUP(TRIM($A2)&TRIM(B$1),$C$2:$D$7,2,FALSE),0)
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.
Glad I didn’t embarrass myself further. I really should learn not to post after 10 pm. 
KneadToKnow:
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.)
Then you get to use this formula
=IF(COUNTIF(OFFSET(Sheet1!$B$1:$C$1,VLOOKUP($A2,Sheet1!$A$2:$D$4,4,FALSE),0,1,2),B$1)>0,1,0)
Make sure to set the cell range for the vlookup to be the entire table on sheet 1.
That should do it.
Thanks a lot guys. Multiple solutions above worked.