hi everyone,
so i have a series of columns. in one of the columns, each row contains a number of phrases separated by commas that are also column headings. what i want to do is if the column heading is contained in the row of column a, then 1, if not, then 0.
so I have =IF(A2=B1,1,0) then =IF(A2=C1,1,0) etc. i think i need to put the actual phrases in quotes to draw out of the row and compare to the column heading?
A sample workbook seems a definite must on this....without seeing the info, not sure how to find what you are looking for..
Add a File - click advanced (next to quick post), scroll down until you see "manage attachments", click that and select "add files" (top right corner). click "select files", find your file, select file, click "upload", when the file shows up at the bottom left click 'done"(bottom right). click "submit reply"(remember the 1 MB limit, you may have to crop your file down to get it to a size that can be uploaded...)
A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
-Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
-To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
If you received helpful response, please remember to hit the * of that post
i thought so ...here you go ...so i want to return a value of 1 if true and 0 if false in corresponding column if contents of rows a2:a13 match any heading
In B2 cell
Formula:
Please Login or Register to view this content.
Drag it down and right...........
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
Try in B2
=IF(ISERROR(IF(B$1="",FALSE,SEARCH(B$1,$A2)>0)),FALSE,IF(B$1="",FALSE,SEARCH(B$1,$A2)>0))
or for 2007 +:
=IFERROR(IF(B$1="",FALSE,SEARCH(B$1,$A2)>0),FALSE)
Hope this helps
darn...I went wrong way...:
=IF(ISERROR(IF(B$1="",0,SEARCH(B$1,$A2)>0)),0,IF(B$1="",0,SEARCH(B$1,$A2)>0))*1
=IFERROR(IF(B$1="",0,SEARCH(B$1,$A2)>0),0)*1
Edit-
first formula can be shortened to this :
=IF(ISERROR(SEARCH(B$1,$A2)>0),0,IF(B$1="",0,SEARCH(B$1,$A2)>0))*1
and forgot to mention---
Drag down and across
Last edited by dredwolf; 01-18-2013 at 01:10 AM.
B2, copied down:Originally Posted by sydney72
=ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH($B$1:$O$1,A2),$B$1:$O$1))+0
You could also use FIND instead of SEARCH, but FIND is case sensitive (ie- "finish" does NOT = "Finish" with FIND, but it DOES with SEARCH)
thank you all! dredwolf, worked beautifully!
You are welcome
Please remember to mark the thread as solved if you are satisfied with your solution :
To mark thread "Solved", go to the top of the thread,click "Thread Tools",click "Mark as Solved"
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks