If statements

[Graysleaf]

Good morning,

I would like to know how to write an IF statement that is capable to do the following:

=IF(C3:I8)(Sheet1) contains "5229" then copy A3 of same row(Sheet1) and paste to (Sheet4) B2

Any ideas?

 

[Yongle]

This assumes that value 5229 appears once only in the entire range

Perhaps this ARRAY formula in cell B2

=INDEX(Sheet1!A3:A8,MATCH(1,(Sheet1!C3:C8=5229)*1+(Sheet1!D3:D8=5229)*1+(Sheet1!E3:E8=5229)*1+(Sheet1!F3:F8=5229)*1+(Sheet1!G3:G8=5229)*1+(Sheet1!H3:H8=5229)*1+(Sheet1!I3:I8=5229)*1,0))

- copy the formula into cell B2 in Edit mode
- and do NOT hit {ENTER}
- use {CTRL}{SHIFT}{ENTER} instead

It will look like this but you cannot type in the { }
{=INDEX(Sheet1!A3:A8,MATCH(........1,0))}

 

[Graysleaf]

Thanks for your quick reply I got the {} to look correct but B2 indicates #N/A
I don't see Sheet4 in array also should there be a space on above " She et1!G3:G8=5229" ?

 

[Graysleaf]

Thanks for your quick reply I got the {} to look correct but B2 indicates #N/A
I don't see Sheet4 in array also should there be a space on above " She et1!G3:G8=5229" ?

 

[Graysleaf]

I need 5229 to appear in several cells through-out entire range of sheet1. Any cell containing 5229 within range needs to copy column A of same row and paste to different page; Sheet4 B2

 

[Yongle]

Reply to post4
Why should sheet 4 be in the array?
(Sheet 4 is only relevant for the single final value which goes in cell B2 of that sheet)

My interpretation of what you said
Look in (Sheet1)(C3:I8)
Look for 5229
If found then get value of SAME ROW as 5229 in (Sheet1)(column A)
Place value from column A in (Sheet4)(B2)
which is what it does

then copy A3 of same row(Sheet1)

Makes no sense (A3 has one row = 3)
- I took it to mean Copy Cell in Column A In same row as 5229

should there be a space on above " She et1!G3:G8=5229" ?

I think you can answer that yourself

 

[Yongle]

I need 5229 to appear in several cells through-out entire range of sheet1. Any cell containing 5229 within range needs to copy column A of same row and paste to different page; Sheet4 B2

- I would do that using VBA

 

[Graysleaf]

LOL thanks working it now

 

[Graysleaf]

When array is entered then ctl shift enter is selected I get a window that pops up
Update Values:et1
as if it wants to save as file somewhere

 

[Graysleaf]

Ok thanks, I'm not so familiar with VBA. Thought there might be a simpler formula. But thanks anyway