I have an Excel sheet with two columns. The first column holds names. I also have a list of names as text file, now added as second sheet.
For each row, I want to set the second cell to a fixed value if the value of the first cell is present in the list. Other rows should remain unchanged.
I was told to consider VLOOKUP but could not make up a formula.
I'm using Excel for Microsoft 365
first sheet: 
column M is the one to be changed
second sheet: 
The expected output would simple have entries in column M to be IN instead of OUT.
CodePudding user response：
You can do it with MATCH if the Second Sheet is in the same workbook. If it is in a separate workbook, it will still work, but only of the other workbook is open.
=IF( ISNUMBER( MATCH( A2,Sheet2!$A$2:$A$7, 0 ) ), "IN", "OUT" )
Alternatively, if you don't want to do VBA, you can pull the text file into a table in your workbook that is hidden into the data model and then reference it, but that is a long trip and can only be done on an installation that supports Power Query.
CodePudding user response：
Try COUNTIF:
=IF(COUNTIF(List!A:A,A1),"IN","OUT")
CodePudding user response：
Formula would be somethin like
=VLOOKUP(K3,Table1,2) Where Table1 is your look up, K3 is the value to look up (index) and 2 is the second column you want to return
