Return Yes If Text Is Found Within A Cell
Dec 24, 2013
I currently have the below formula to search for text of interest (A1) within each cell of a column (B6:B30).
=IF(ISERROR(MATCH(A1,$B$6:$B$30,0)),"NO","YES")
The formula works perfectly if there is only a single value in the cells. But not so well when the cell has multiple values separated by a comma.
For example:
Column B
X
Y
Z
T, X, Y, Z
It would return a "Yes" for X, Y, and Z because those are found individually. However, T will appear as a "No", because it is found within a cell that is separated by commas.
View 4 Replies
ADVERTISEMENT
Feb 27, 2012
The formula has to include a range across each row (for each student). The formula has to search for specific text contained in the classnames, and then return the result along the same row.
Below is an example, which I hope saves OK in the thread. If not I'll upload an Excel file:
A B C D Results:-
1 class1 class2 class3 class4 Gg Hi Fr Sp
2 10m/Gg1 10m/Hi2 10m/Fr1 10m/En1 TRUE TRUE TRUE FALSE
3 10n/Hi1 10n/En2 10n/Sp1 10n/Ma1 FALSE TRUE FALSE TRUE
View 14 Replies
View Related
Sep 15, 2006
Im trying to create a formula that check to see if the value in H4 equal any value in the vlookup table m4:m48, if so, then Y, if not then N. the following does not work: =IF(ISNA(MATCH(H4,M$4:M$48)),"N","Y")
View 2 Replies
View Related
Apr 29, 2014
I am looking for a formula to return the column number of the array when a specific text is found, in this case the text is "Yes"
The Array will only ever be 5 cells beside each other in a row
for example, M4:Q4, will be as follows - No No No Yes No
I want a formula to look at these 5 cells and return the number 4 as that is where the "Yes" value is
View 5 Replies
View Related
Jun 13, 2013
I'm using a formula to lookup names within text and return that name to a separate column if it's found.
The formula is =INDEX($E$1024:$E$1026,MATCH(FALSE,ISERROR(FIND($E$1024:$E$1026,E1010)),0))
I understand index and match functions, but I'm confused regarding the use of FALSE as the lookup value and the iserror(find( usage.
View 2 Replies
View Related
Apr 28, 2006
on one sheet we have a summary of the main list, which includes totals of money recieved, totals of all the different sources (ie, where they heard about us from), the totals of the frequencies they pay (ie, how many donate monthly, quarterly...) ... etc. on the next sheet we have the "main" list of donors, their IDs, amounts, frequency, source ...
the totals on the first sheet are updated manually, but i want to change that as there are a great number of errors.
View 8 Replies
View Related
Apr 11, 2012
I've been given a spreadsheet that is poorly constructed but I'm not allowed to alter.
The layout is:
Box Name Box Date ID1 ID2 ID3 ....
Where there is not a fixed number of IDs in each box
(There are five different sheets so I'll have some fun concatenating results)
Given a list of IDs I need to return the box name and box date that the ID's file sits in. The spreadsheet has blanks everywhere and the numbers contained in rows are non-consecutive and smaller values can appear later in a column than larger ones.
I think I need some combination of index, offset and maybe sumproduct? TBH, I avoid sumproducts like the plague, preferring to utilise sumifs and dynamic named ranges since this improves clarity so I'm basically a newb at them.
Any skeleton framework that you think would work to return the text values for the LHS where the number could be anywhere? Once I can see a rough example I should be able to get to grips with the logic and wrangle it into the necessary solution.
View 5 Replies
View Related
Jul 2, 2009
I want to return a result IF a certain value is found in another cell. I've attached a sample workbook, but here's a description:
On the "Inventory" worksheet, in column H, I want to lookup the quantity in column B from the "Warehouse Response" worksheet. If the Part # in column E of the response sheet is NI1088106, I want THAT value to show under the NI1088106 part number, and I want the quantities for the NI1089106 to show under that part number. Ideally, the result of the formula would show a "0" if no data is found.
Here's the formula I tried beside the NI1088106 data, but it's not giving the desired results: =IF('Warehouse Response'!E:E="NI1088106", VLOOKUP(LEFT(A3,3),data,2,0))
View 3 Replies
View Related
Apr 14, 2013
I know I can do this by nesting a load of if statements but I was wondering if there is an easier way.
If in cell A2 I have the value 12 and I want cell A1 to return a value FOUND if any cell from A6 to A24 has the value 12 in it.
View 3 Replies
View Related
Oct 26, 2006
I need to pull data from Column C by looking at Column A. However it's not as simple as that. Under Column A, there are various fields, the first number represents a type of product and under what year it is currently in. So "300100" would represent a 3 yr product that is in it's third year, "210000" would be a 2 yr product in it's first year and so on. The problem is that when I pull data from the DB, some 3 yr products are flagged as products that are in their 4th or 5th years, which isn't possible. So I created an IF statement that takes this into account and ignores illogical combinations.
=If(A2="300100",C2,If(A2="301000",C2,If(A2="310000",C2,If(A2="210000",C2,If(A2="201000",C2,If(A2="510000",C2,If(A2="501000",C2,If(A2="500100",C2,))))))))
As you can see, I don't have the conditions "500010" and "500001" because it won't fit into the IF statement. Is there another way to go about doing this task? I've searched up the forums and someone recommended using the IF statement with VLOOKUP but I don't think it works in this particular case since I have 10 conditions.
View 2 Replies
View Related
Jan 8, 2012
I have long text values that include names in cells A1:A100.
In cell D1:D10, I have a list of names that I would like to check if any of them is found in each of the cells in A1:A100 and if yes, return TRUE in column B.
So in a way, what I need is a a bit like the SEARCH function, only that I need to find multiple FIND_TEXT values. If just one of the names in D1:D10 is found, the formula should return TRUE.
View 4 Replies
View Related
May 27, 2014
Looking to find 1 of 2 words in a cell in column B and return the word found in the same row in column E. This seemed easy but I am not having any luck.
the cells in column B have several words in them but I am looking for 2 specific words "PLAT" and "ORIG". If the word is not in the cell, it should show a blank cell in column E in the same row, otherwise one of the 2 words should be in that row in column E. A VBA loop would be ideal but a formula that can do it might work as well.
View 3 Replies
View Related
May 15, 2012
So I'm trying to merge some of the information found within two separate worksheets. I'm working with a list of ID#s that may or may not be found on both worksheets, and in a column on Worksheet1 I want to put some of the information found for the corresponding ID# on Worksheet2.
Now I've figured out how to check if an ID# on Worksheet1 exists within a column in Worksheet2 using the formula:
IF(COUNTIF('WORKSHEET2'!$A:$A,A2)0,"MATCH","NO MATCH")
(Excuse the syntax errors if they exist, I'm doing this off the top of my head for expediency)
However, instead of returning the word "Match" if it does indeed exist on Worksheet2, I would like to return the value found in Column E, on the row of the matched ID#, in Worksheet2.
View 6 Replies
View Related
Jan 31, 2008
Find a short text string in a column of longer text strings and when that short text string is found return the longer text string that matches.
View 3 Replies
View Related
Feb 12, 2009
Cell B4 is selected by user and then runs the macro. The macro then looks UP (A3, A2, A1 etc) cell by cell until it finds the first cell that has a valid date in it and if that date is same as the date that the macro is being run pastes "This Text" into the comments section (Not replacing everything in the comments but simply adding to it.)
Column A
2/20/2009
(empty cell)
Dave
Jonathan
Steve
2/21/2009
James
Sally
Tyler
(empty cell)
John
Betty
View 5 Replies
View Related
Dec 21, 2009
What I would like to do is if a string e.g. "*12/18/09*" is found in column A, then place "1" in column B. There are many rows that I would like to search. Either a formula or VBA would be fine.
View 6 Replies
View Related
Nov 29, 2011
I need a macro or formula that can parse a column of data and if it finds a specific string of text ,then it changes another cell on that row to zero. It can have multiple text variables but all return the same value. For Example,
if
artisan - matte - flat black
artisan - matte brushed gold
small - canvas - flat black
is found in a longer string of text
then it should change another cell on the same row to have a value of 0. Also i should mention that i need it to loop as it will be parsing through potentially thousands of rows.
View 6 Replies
View Related
Oct 1, 2008
I have a file that the user selects and when they enter a value (in this case, a job title) into the input box, my macro looks for the value in that file. If the value is there, a msgbox pops up that lets the user know that the value was found and it then goes to that cell, displaying in the next msgbox the cell address of where the value was found. I already have the code down for this part of the macro.
My problem is what happens if the file has the same value more than once. Ideally, I'd like to display a message box that returns the addresses of both cells with the same value and then prompts the user to select one of these values as the value they are looking for. I am not sure if a msgbox or a msgbox and then an input box are most appropriate for this situation. Once the user does this, the macro continues. The rest of my macro is built on the cell where this value is, so it is crucial that I make sure there is at least one value selected. Any help is much appreciated. I have included a sample worksheet of what this situation might look like.
Here is the code I presently have for this part of my macro.
Sub GetOpenFileName()
strFind = InputBox("Please enter the job title you wish to search for:", "Search for job title in this file")
If strFind = vbNullString Then Exit Sub
With Sheets(strSheetsMainCompProfile)
If WorksheetFunction. CountIf(Range(Cells(1, 1), Cells(100, 100)), "*" & strFind & "*") = 0 Then
MsgBox strFind & " cannot be found on this sheet"
Else
View 9 Replies
View Related
May 10, 2008
I am trying to clean up and simplify my macros by compartmentalizing frequently repeated commands. I like to search for text in column headings and define the cell containing it as a range that I can later use .column or .row commands on, so my macros are a bit more flexible. The code I currently use for this is
Dim XColumn As Range ‘the cell containing the text “X”
Cells.Select
Selection.Find(What:="X", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Set XColumn = ActiveCell
I would like to be able to create a Macro (possibly name FindSetCell()) that will accept arguments and achieve the results of the above code in other macros
I think the command might look something like FindSetCell(“text to be searched for”,”name of newly Defined Range”) But I have not figured out how to code it
View 2 Replies
View Related
Aug 30, 2012
I am trying to make a macro that will query a column J4 for a date, if the date is found say "Wednesday, July 4, 2012" (J8) get the text from Column K8 "Independence Day" and insert the text in Cell (H1).
The Macro Prints sign in sheets with the date in H2, it only prints Monday through friday. Then skips the weekend and the next date will start the following Monday.
The Federal Holiday schedule is in column range J (Date) and K (Holiday)
This is where I am stuck ....
EmployeeTimeSheet_2012.xlsm
View 2 Replies
View Related
Jan 28, 2014
I have 2 tabs in a 2013 workbook. Inventory Receipts and lookups. One of the Data verification lookups I have is a drop down list in each cell in Column B (eg: Cat, Dog, Mouse) In the lookups tab I have another cell range containing the sounds (eg: Meow, Bark, Squeak).
What I am trying to acheive is, if B2 contains Cat, then return Meow in B3, if B2 contains Dog then return bark in b3 and if B2 contains Mouse return squeak in B3. Ideally I would like the formula to return the text from my lookup sheet (eg: 'Lookups'!C2,'Lookups'!C3,'Lookups'!C4). So depending on which option they choose from my animal drop down list .. the correct noise would automatically fill in.
View 9 Replies
View Related
Mar 25, 2009
I'm using VLOOKUP to search for results in another worksheet. What I need is 0 returned if the search fails. Currently I'm getting #N/A and that's causing my other formulas to fail.
View 8 Replies
View Related
Aug 25, 2007
I am trying to compare a single cell's Value in 1 worksheet to try to find a match in another column (or a range of cells). If I find a match I want the result to be a 1 and if it is not found then a 0.
Is this the Lookup function?
View 8 Replies
View Related
Dec 3, 2012
I am trying to look in B1:B100 for the Cell that matches A1 and C1:C100 for the cell that matches A2 then return the value from D1:D100 that meets the 2 criteria.
View 6 Replies
View Related
Aug 8, 2006
I am looking for a function that will search a range like 101-199, 200-249, 250-299 and so on.... Say I have a list of numbers like 155, 179, and 210. How can I find out what range they belong to? I am kind of looking at SumIf's but I cant seem to get that to work.
View 3 Replies
View Related
Mar 15, 2008
A have a table like this:
X 1 2
A 9 5
B 8 4
I would like to have a condition that when is equal to B and 2, return the corresponding paragraph in the matrix (in this case 4)
View 5 Replies
View Related
Feb 28, 2013
how to Chk the text string in particular cell, compare it with a super set column and get the full from of the text string from another corresponsing column and the output will be corresponsing full form of the chked text string?
View 6 Replies
View Related
Apr 1, 2014
I am trying to use a formula similar to VLOOKUP() to look in a table for two values and if both values are found, return another value.
Sheet2 is an example of my output file, sheet one is a generated file. i would like the vlookup formula in sheet 2 cell D2 and down to be something like this,
=VLOOKUP(A2,Sheet1!A:K,10)
But I would like to lookup A2 and B2, and the row that contains both values, return the value in the J column
View 3 Replies
View Related
Jan 11, 2006
If lookup doesn't find a match in the first column, can I get it to return an
"ERR" or "0", something that would let me know there was no match? Right now
it returns the value in the second column of the closest value.
View 13 Replies
View Related
Mar 13, 2014
I have an Excel Worksheet with 80,000 lines on it. The Columns are arranged thus:
Col A
Col B
Col C
Col D
Col E
Col F
Code
Description
Cost
Rate
Mobile
0.13
1234
Australia Mobile
0.12
Not Mobile
[Code] ........
I am looking for an Excel Formula that will look up Australia & Mobile and then return the highest value of the range of cells it finds that meets that criteria and enters that value in the Rate column next to mobile (F3).
Then I just want a variant of the same formula that will look up Australia but exclude Mobile and then return the highest value of the range of cells it finds that meets that criteria and enters that value in the Rate column next to Not mobile (F4).
View 1 Replies
View Related
