• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

INDEX MATCH to select items in list


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.
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.
Thanks for your help


  • 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


  • 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


  • mark1-index-ETAF.xlsm
    9.5 KB · Views: 7
what was the solution , if different to my suggestion, please post as people searching may want to see the solution you found
Last edited: