Request: Assistance with arrays

Hi all,

Is it too late to say Happy New Year? Hmm.

I have a challenge. I'm trying to write a formula that evaluates multiple conditions and then places a value in an array.

The logic is as follows:

1. Verify the "Status" (Column C) is "Won"
2. If "Won", look in "Month Signed" (Column A) and identify Month
3. Find Month in Columns D - O
4. When Exact Month is identified, copy Value (Column B), in to corresponding Month.

I have attached an example of the file i'm working on for reference.

Thank you all in advance.

Cheers, Sean
Request Assistance.xlsx

Try

=IF(AND($C2="Won",$A$2=D$1),$B2,"")

Copy across and down

Put this formula in D2:

=IF(AND($C2="Won",$A2=D$1),$B2,"")

Copy across to O2, then copy D2:O2 down as far as is needed.

Hope this helps.

Pete

Hi John,

Thank you. I copied that formula in and it works for the first row; however, in the second row I type in cell A3 Mar and it still puts the 200 (B3) in April.

Any advice?

Cheers, Sean

John has the term $A$2 in his formula - it should be $A2.

Hope this helps.

Pete

Hi Pete,

Thank you! I just figured that out

If i understand the formula correctly - you are referencing A2 to each cell from D1 to O3. Is that correct?

Cheers, Sean

On a particular row, the cell in column C should be "Won", and the cell in column A should be the month in row 1 (for columns D to O), and if this is the case then the formula will return the value from column B, otherwise it will return a blank.

Hope this helps.

Pete

Yes I realised I had put $A$2 but was busy so could not reply immediately.

@Pete: thank you.

Sean,

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

Hi John and Pete,

Thanks again! Reputation added and problem resolved.

Cheers, Sean

Thanks for doing that, Sean.

Pete