An if statement question

[Flashart80]

Hi all.


I have a spreadsheet with a cell that holds an account name entered by the user in cell $Z$2. I then have this formula =OFFSET(INDEX(lastwk_account:lastwk_campaign,(MATCH($Z$2,lastwk_account,0)),2),0,) which returns the 1st campaign from a database list held in column lastwk_campaign. I then have this copied down 40 or so cells to make sure I return all the campaigns. However it also returns campaigns that do not belong to the account in $z$2.


Somehow I need to construct an if statement that says "if the account names in lastwk_campaign does not match $z$2, return a blank cell".


Does anyone have any ideas?


Thanks

Peter

 

[Luke M]

Array formula (Ctrl+Shift+Enter to confirm):

=IF(ROWS($A$2:A2)>COUNTIF(lastwek_account,$Z$2),"",INDEX(B:B,SMALL(IF(lastwk_account=$Z$2,ROW(lastwk_account)),ROW(A1))))


Note that I had to reference an entire column (B:B) instead of lastwk_account. If lastwk_account already is the entire column, you can use that instead. Drag this formula as far down as would be needed.

 

[Flashart80]

Hi Luke and thanks


I have amended the formula to reflect the columns and rows and it gives me the blank cell so I must have fudged it somewhere. Would you mind taking a look?


{=IF(ROWS(lastwk_account)>COUNTIF(lastwk_account,$Z$2),"",INDEX(lastwk_campaign,SMALL(IF(lastwk_account=$Z$2,ROW(lastwk_account)),ROW($Z$2))))}


Column R is lastweek_account, $z$2 is the account name, lastwk_campaigns is the list of campaigns. I think the forumla is nearly there. The formula is starting in AA10 if that means anything.


thanks for your help

 

[Luke M]

=IF(ROWS($A$1:A1)>COUNTIF(lastwk_account,$Z$2),"",INDEX(lastwk_campaign,SMALL(IF(lastwk_account=$Z$2,ROW(lastwk_account)),ROW(A1))))}


If lastwk_campaign is the whole column, that's good. I should have clarified a few things in first post. The first ROWS function is there as a counter. The actual range it references doesn't matter, it just needs to start out referencing 1 cell and grow as you copy the formula down. It's checking to see how many records have already been returned. The last ROW function, where it says ROW(A1) is also a type of counter. It starts off saying to return the 1st record, then when you copy it down and it changes to ROW(A2), it pulls the 2nd record, and so on and so on.

 

[Flashart80]

Ah! You genius! It works! It's a little slow but I'm not overly worried about that. That will teach me to muck about with formulas!


Many thanks,I really appreciate your help!

 

[Luke M]

Hurrah, glad it's working for you. Always glad to share some tricks. =)