Make it mark "V" if found mark "X"

[muhammad susanto]

hi all..

how to make formula to determine mark "V" (checklist) if in a range e.g range D3:I6 found mark "x"
my "x" in always in different position cell (random)
like this layout :

given	given	given	given	given	given
x	test1		test5		test9
	test2		test6		test10
	test3		test7		test11
	test4		test8		test12

<tbody>
</tbody>

any help, greatly appreciated

.sst

 

[Yongle]

You could use a helper column

Excel 2016 (Windows) 32 bit

	D​	E​	F​	G​	H​	I​	J​	K​	L​	M​	N​
1​								formula in J3 copied down			formula in N2
2​	given	given	given	given	given	given	HELPER		my text	my text is found	=IF(SUM(J:J)>0,L2&" is found","not found")
3​	my text	test1		test5		test9	1​	=COUNTIF(D3:I3,L2)
4​		test2		test6		test10	0​
5​		test3		test7		test11	0​
6​		test4		test8		test12	0​

Sheet: Sheet4

Excel 2016 (Windows) 32 bit

	D​	E​	F​	G​	H​	I​	J​	K​	L​	M​	N​
1​								formula in J3 copied down			formula in N2
2​	given	given	given	given	given	given	HELPER		this text	not found	=IF(SUM(J:J)>0,L2&" is found","not found")
3​	my text	test1		test5		test9	0​	=COUNTIF(D3:I3,L2)
4​		test2		test6		test10	0​
5​		test3		test7		test11	0​
6​		test4		test8		test12	0​

Sheet: Sheet4

 

[muhammad susanto]

sorry, i missing it...
i try like this =if(range,="X";then change with "V")

note:
range e.g. D3:I6 not in single cell

 

[Yongle]

You can place a formula in cell D3 and say
IF "X" is found in E3:I6, then D3 is "Y", else D3 is "X")

BUT you cannot REPLACE the value in a cell in the way you want - the ONLY way to do that would be to use VBA

 

[muhammad susanto]

hi Yongle, thank your advice...