Excel if range of cell contain a text put a cell value in

Solved/Closed
diegomagum Posts 4 Registration date Tuesday September 16, 2014 Status Member Last seen October 1, 2014 - Sep 16, 2014 at 03:56 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Jul 13, 2021 at 12:07 PM
Hi All,

How do I put a cell value into a cell when the range of cell contain a specfic text.
For example, in the sample below, I want to put the cell value (13/2/2014) into B3 when a range of cell (A) contain Sam.




A1 Tom
A2 13/2/2014
A3 Sam
A4 15/6/2014

B1
B2
B3 13/2/2014
B4


Is it possible as well that I can copy the cell value (13/2/2014) to all fields in column B till it reaches TOM (A1) for example?

Please enlighten me.
Thanks
Related:

3 responses

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Sep 16, 2014 at 10:56 AM
Hi Diego,

Your first question can be solved by using this formula in B1 and then drag it down as far as needed:
=IF(A1="Sam","13/2/2014","")

Your second question is a little strange, since Tom is at the first row. Meaning the value "13/2/2014" will not be entered at all.

Best regards,
Trowa
2
Hi Trowa,

This really helped! May I know if you can also put many items on the "SAM" part? Like =IF(A1="Sam,Tom,Kim,Sean","13/2/2014","")? Thank you!
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552 > Jess
Jul 13, 2021 at 12:07 PM
Hi Jess,

Sure, we just need to add in the OR function:
=IF(OR(A1="Sam",A1="Tom",A1="Kim",A1="Sean"),"13/2/2014","")

Best regards,
Trowa
0