Please can you show me how to create an IF statement based on a dynamic range that returns TRUE if it finds the value 'ticket' in ColumnB so that it it stops at the cell either above first blank cell in ColumnB or the cell above where it finds a cell in ColumnA that begins with the word "Issue". "Issue" is a prefix to a number so there will be Issue1, Issue 2, Issue3 etc which is why it needs to look for a cell in column A that begins with "issue" rather than equals "Issue".
Thank you
Hi,
Would you be able to post a small sample workbook with a few examples and, importantly, your desired result in each case?
Regards
Please find example attached.
Thank you
Try this array formula (important that you know how to enter this type of formula in Excel) in C1 and copy down as required:
=IF(ISNUMBER(SEARCH("Issue",A1)),COUNTIF(B2:INDEX(B1:B$10000,MIN(MATCH(TRUE,INDEX(B2:B$10000,,)="",0),IFERROR(MATCH(TRUE,ISNUMBER(SEARCH("Issue",INDEX(A2:A$10000,,))),0),10^10))),"Ticket")>0,"")
Regards
That's great, thank you. How would I adapt this to return the word "Ticket" rather than TRUE?
Thanks
No worries. And what if it's FALSE? Still FALSE? Or another blank?
This will do the former:
=IF(ISNUMBER(SEARCH("Issue",A1)),IF(COUNTIF(B2:INDEX(B1:B$10000,MIN(MATCH(TRUE,INDEX(B2:B$10000,,)="",0),IFERROR(MATCH(TRUE,ISNUMBER(SEARCH("Issue",INDEX(A2:A$10000,,))),0),10^10))),"Ticket")>0,"Ticket"),"")
This the latter:
=IF(ISNUMBER(SEARCH("Issue",A1)),REPT("Ticket",COUNTIF(B2:INDEX(B1:B$10000,MIN(MATCH(TRUE,INDEX(B2:B$10000,,)="",0),IFERROR(MATCH(TRUE,ISNUMBER(SEARCH("Issue",INDEX(A2:A$10000,,))),0),10^10))),"Ticket")>0),"")
Regards
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks