# INDEX MATCH to select items in list

#### mdavid

##### Member
Hi, I have a column A3:A2249 of item numbers, item number appear multiple times in this column.
There's an index column B3:B107 where selected item numbers appear once.
If an item in column A appears also in column B, then insert "1" into column C else insert "0".
I think I can do that with the following formula in column C:
=IF(INDEX(??????,(MATCH(?,??:??,0)),"1","0") - but need help with the INDEX and MATCH, and maybe alot more!
Any help much appreciated.

countif()
countif(\$A\$3:\$A\$2249,B3)
copied down - will give a 1 if only in a once, and 0 if not found

OR include in a IF - to only give a 1 no matter how times it appears in A
=IF( countif(\$A\$3:\$A\$2249,B3) >0, 1 , 0)

Thanks for this Etaf,
I tried the 2nd option (=IF) - which is what I want.
Seems to be marking all the items in col B with a 1, instead of marking items in A that appear in B with a 1
I've uploaded the file with the results.

#### Attachments

• mark1-index.xlsm
59.2 KB · Views: 6
sorry should have been
=IF( COUNTIF(\$A\$3:\$A\$13,B3) >1, 1, 0)

count will find itself 1 - so >0 will always be 1

i have reduced the 1000's of rows to a small sample so easily seen

#### Attachments

• mark1-index-ETAF.xlsm
9.3 KB · Views: 10
Still having problems.
In the sample you sent me, the results are not consistent.
For value 4 in A:5 C:5 = 1 (which is correct), then for 4 in A10 & A11 C is marked 0,
Same for value 3 in A7:A9.

If an item in column A appears also in column B, then insert "1" into column C else insert "0"
All items in col A appear in col B, so, the result is 1 everywhere as described in post #2. Your requirement is confusing ( BTW "1" is text and 1 is a number. Is this important?).
If you insert a number in col A that is not in col B then it returns a 0 ( see attached)
So ETAF's first solution is correct

#### Attachments

• mark1-index-ETAF.xlsm
9.5 KB · Views: 7
Thanks pecoflyer,
Sorry it's confusing. Found a solution.