Hi All,
I hope this message finds you all well. I'm currently looking for a method by which to search a column for a specified value and return all results that DO NOT match match that value. Example, column A=Client Name (A1=John Smith, Esq., A2=Jason Smith). I would like to search column A for any cells that do not have ", Esq." (i.e. it returns A2). How would one go about this?
you could try this array formula. Make sure to use CTRL + SHIFT + ENTER when using this formula. If done correctly { } will be placed around your formula.
=INDEX($A:$A,SMALL(IFERROR(FIND(", Esq",$A$2:$A$30)>0,ROW($A$2:$A$30)),ROW(1:1)),)
Drag down and it will list if there are multiples
<----- If you are happy with your solution please click on the "* Add Reputation" as a way to say thank you.
Wow, thanks so much Dosydos! It works, however, how would I get it to return the cell where the instance occurs (instead of telling me what's in the cell, tell me where the cell is)?
Another Array function so make sure to use CTRL + SHIFT +ENTER
="A"&SMALL(IFERROR(FIND(", Esq",$A$2:$A$30)>0,ROW($A$2:$A$30)),ROW(1:1))
kind of a cheesy way i did it. IF your Client names aren't in A then just change the Red A to what ever column they are in. Also make sure the bolded row ranges stop with your client name data. Make sure there are no blanks at bottom because the logic for a cell not having ", Esq" and being blank is the same, so it will give you extra cells.
Wow! Dosydos you're amazing. You've really saved me quite a bit of headache with these array functions.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks