check for data

New Contributor

an easy question, but beyond my simple Excel knowledge:

I have a spreadsheet with 26 rows and about 150 columns of text content. I would like to create an overview in a second spreadsheet, which states whether a certain value x in row 1 occurs on the other sheet. Output = 1 if true and =0 if false. What is the easiest way to do this?

Thanks!

3 Replies
Try =MATCH(A1,Sheet1!A1:A26,0)
thanks a lot! and what do I do if I want as an output 1 and 0 for no match found? With this formula I get 1 for yes it's a match and #NV for no match

@NinaZoe 

=COUNT(SEARCH(A1,sheet1!$A$1:$C$3))

An alternative could be this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021. The range of the formula can be adapted as required.

sheet1.JPGsheet2.JPG