Changing multiple cells depending on another cell in their row

user_1436954 0
Viewed 46

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: enter image description here column M is the one to be changed

second sheet: enter image description here

The expected output would simple have entries in column M to be IN instead of OUT.

excel
3 Answers

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" )

enter image description here

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.

user_46171210

Try COUNTIF:

=IF(COUNTIF(List!A:A,A1),"IN","OUT")

enter image description here

user_121888610

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

[Screen shot of forumal in Action1

user_179236440
Related