# Return True If Any Values In List Is Found Within Cell

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.

## Return TRUE If Part Text Found In Cell Range

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

## Formula To Return Number Of Continuous True Values At The End Of A List

Apr 9, 2014

I have two data columns: the first is a list of the last 100 calendar dates, and the second is an ON/OFF (or 1/0 if you prefer) value corresponding to each date. If the last value in the second column list is ON, I want to find how many continuous days the value has been ON for. If the last value in the second column is OFF, I want it to return a zero. Note that in the 100 day span of data, the value may switch between ON and OFF several times, but I only want the continuous, uninterrupted ON days at the end of the date range.

## Return A List If True

Sep 12, 2013

As a 'true' result of an 'IF' statement, I would like to return a data validation list, as a false result, the term "Not I/C"

The below isn't right, but its the best explanation of what I'm trying to do........

=if(A1="Yes",(the list, eg B1:B5),("Not I/C))

## Return True / False If String Of Words Is Present In Another List?

Dec 5, 2013

I have two lists, one that is 99,000 lines and the other is 150. I am trying to find out where the word(s) in the list of 150 is present in the list 99,000.

1st list of 99,000 is in Sheet1 A1-A99000 and the second list is in Sheet2 B1-B150.

The caveat is that in column A there is additional words in that cell so you can not do a simple vlookup, because there may or may not be an exact match.

I have been stuck for hours and the best i can get is to use the match function but it is not working because it is not exact. Last thing i want to do is use the CTRL F key to lookup all the words.

## Formula To Return True/False If A Row Has 6 Contiguous 0 Values

Sep 25, 2009

I have number data in columns F through AK. In column AL I want a formula that will look in columns F through AK from the left to right. Once it identifies a positive value I want the formula to identify if after that positive number there are any occurances where there are 6 0's in a row (anywhere up until column AK). The formula can return a True or False. I want to drag the formula down across many rows of data. If the entire row contains 0's, I do not want it identified as a TRUE

## Formula To Lookup Two Values In A Row And Return Value If Both Are Found

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

## Return Data Adjacent To Found Values

Jul 30, 2008

Is it possible to write some VBA code that will copy IDs from Sheet1 and then find these IDs in Sheet2 and return the values these IDs have?

## VLookup Multiple Values And Return Only Smallest Value Found

Nov 27, 2013

I have an excel sheet with multiple columns. In one column I have X's and in another I have a limit. I need to look down the stuff column, find every X, match each X to its corresponding limit and then compare all the limits and return only the smallest limit found. I.e if I was looking down the stuff column I would find 4,6,8,8,3 and thus I would return 3 as my value. I am very new to working with excel formulas,

limit
something
stuff
4
x

[Code]....

## Lookup Column Values In Another & Return Value If Match Found

May 28, 2008

I need to create a macro that will loop through a list of names on Sheet 1 / Column A, and if that name is listed on Sheet 2 / Column A, then it will set Sheet 1 Column B (for that one name) to "1".

The problem I've run in to is that both the lists change in length each time I have to run this (so no set range).

I've been able to get this to work with two ( nested) loops, where the first loop goes through the Sheet 1 list and the nested Loop looks for the name on Sheet2, but it is extremely slow.

Is there any simple VBA code or formula that I'm missing that could do this? I've tried to do something with the " Find" VBA command, but I can't see how it would apply to this if the list changes in size.

## Return Values Of Adjacent Cells When Condition Is True Omitting Values Where Condition Is False?

Jan 15, 2014

Here's a simplified example:

ColA
ColB
ColC

Row1
A
Y
A

Row2
B
N
D

[Code] .........

I'm looking to return the values in column A adjacent to the cells in Column B equal to "Y". The kicker has been returning only the cells where the condition is true. Column C displays the desired behavior.

The closest I've been able to get is with a simple IF statement but I'm pretty sure the answer is a far cry away from there and likely requires an array formula. I'd prefer not to use VLOOKUP or OFFSET but will if the alternative is very complex.

## Creating List Of True Values From Column

Mar 13, 2013

In the attached spreadsheet I would like to create a list of part numbers if column D E & F all have a #N/A value. Right now I'm using a True/False but this list contains around 3000 items and I don't want to have to scroll down to find the True values. Is there a way I can just create a list of corresponding part numbers to the True Values?

SAMPLE.xlsx

## List All Values Found Within A Range.

Oct 30, 2008

I have a problem with finding the value in column B that is found within a range eg: <=1 & >=5 and return the corresponding values found in column A. kindly refer to the attached sample for reference.

Sep 13, 2007

I have two worksheets. Worksheet One and Worksheet Two. In Worksheet One Column A is a list of numbers. I want to look for all of those numbers in Worksheet 2 Column A. Any of the numbers in Worksheet One Column A that are not found in Workseeht 2 Column A I want to list in Worksheet One Column B. I do not want to compare cell to cell but if any number in the first column is found in any position in the second column.

## Return True/False If Cell Contains Formula

Apr 7, 2009

Is there a formula or VB code like the =TYPE() function that will return whether a cell contains a FORMULA? Here's the issue:

I'm writing a macro to update 10,000 sheets. Each sheet has a column that I'm updating with a new formula. Some of these formulas have been previously overwritten with a random number that would delete the formula from the cell. I have to leave the overwritten values where there is no formula, and replace the values of the formulas where they are still intact. I assume that if I can identify whether there is a formula in the cell or not, I can choose that cell to skip or update.

## 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.

## Return A Result IF A Certain Value Is Found In Another Cell

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))

## Getting Cell Match To Return FOUND?

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.

## Return Cell Value If Match Found

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.

## Return A True/false Based On A Cell Containing A String Vs A Value

Jul 20, 2006

how return a true/false based on a cell containing a string (text) vs a value?

I have a column where most cells are blank, some contain text, and others contain dates. I would like to select and manipulate only the ones that contain dates (or values).

## Return Value To Cell Based On CheckBox TRUE/FALSE

Mar 12, 2008

I would like to create a qestionairre with checkboxes used to answer the question "Do you need____" (Check if yes). On the following workbook I would like a list of all items needed, and a list of items not needed on yet another workbook.

Is there a way to create these lists without having blanks for values that are not true? (and because Im sure there is HOW?)

## Hide Checkbox TRUE/FALSE & Sum Values Adjacent To TRUE

Aug 2, 2009

I have a worksheet where I have around 300 rows, each with 7 columns. What I want to do is add a checkbox to each column. I plan on setting non-applicable checkboxes to mixed status and locking the worksheet. I will unlock applicable checkboxes and sumif or countif their value according to row-based scoring, for example, each checked checkbox represents a value of 3. I do not know VBA and have chose to use the form control checkboxes rather than ActiveX.

I believe that a formula for this would be something like: =SUMIF(B1:B3,True,"3") or =COUNTIF(\$B\$1:\$B\$3,True)*3

I am wondering firstly if I have that right and secondly if there is a way to stop my checkboxes from displaying labels. Currently, if I click on one it displays True behind the active checkbox. If I uncheck it, it displays False.

## Function To Return A "true" If Three Values In An Array Match.

Jan 26, 2010

I need to figure out how to match three values on the "source info.xls" file attached to the "PFG FILE.xls" that is also attached. In column A in the "Source info.xls" file the formula will need to match the yellow column, then the green column, then the red column. keep in mind that the attached files are just a small subset of data. The "PFG File.XLS" is actually 150K rows long.

## How To Return Multiple Values In Multiple Cells If Argument Is TRUE In IF Functions

Jan 8, 2013

I want my IF function to populate two cells with two values if argument for IF function is found TRUE. Is it possible?

IF(logical_test, [value_if_true], [value_if_false])

value_if_true = return multiple values in mutiple cells (for example put number 8 in cell A2 and number 10 in cell A3 if function is TRUE)

How would I do that? I tried putting IF(logical_test, (A2="8",A3="10"), [value_if_false]) but it is not possbile...

## If Cell Is True Then Allow LIST

Jun 22, 2007

I have one tiny qs which I hope someone can answer for me.

I need to allow a user to see a dropdown list (with options) only when the previous cell has the word "complete".

For example, cell B1 can have:
On going
Complete
Blank

If B1 has complete then I want the user to be able to select a number from a drop down list in C1 otherwise I want it to say "wait" or if its Blank to be [Blank].

I tried the Validation > Custom and put in
=IF(B1"Complete","Wait",OptionsList)

Where OptionsList is a defined list of 5->1 but this lot didnt work. I tried similar things in conditional formating but nothing again.

## Looking To Find 1 Of 2 Words In A Cell In Column B And Return Word Found In The Same

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.

## Return Third Value If Cell Is Found Within Another Cell Range

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.

## Check A Cell For List Of Data - If Found Then Print Row

Mar 29, 2014

I have a tab with different city names and then a a series of answers to a number of different questions, what I need to do is create a macro which looks a table on a different tab, and if column b matches any of the words in the table to then print that entire row of answers.

So if the information found in R1!M3:M21 is found in Results!B:B then print that row to sheet R1. If the information found in R2!M3:M22 is found in Results!B:B then print to sheet R2 etc.

This is the only other way I could describe it, however I don't think it can be done as a formula anyway (even though it is somewhat gibberish)

IF(ISNUMBER(SEARCH(R1!M3:M21,Results!B:B))print the row,move onto next row)

## Formula - Count TRUE Values Up To First Blank Cell

Jul 25, 2014

I am designing a spreadsheet for my colleagues and I have run into a problem. Range U16 tu U and the first blank row has values TRUE or FALSE. The problem is you will never know if it's U16:U21 or U16:U90. The thing is to come up with a formula (no vba) that will count all TRUE values in that range. I found a formula on the internet to find the first blank column: =MATCH(TRUE,INDEX(ISBLANK(U16:U300),0,0),0) + 16 - 1

The formula works great and if I put it in, say, cell P2 it will return the correct result.

Now, the cell K2 has the following formula: =COUNTIF(U16:U300,TRUE). It Counts incorrectly. I do not want it to run to U300 but to whatever P2 returns. How can I do this?

## How To Populate Listbox With List Excluding Values Found In Another Listbox

May 27, 2014

I have a userform where I can select multiple items in a listbox and add them to another. I also have the ability to filter the first listbox to make finding items easier. The issue I am having concerns the clear filter button. As currently designed, the clear filter button will reset the initial listbox back to its default values. Ideally, I would like it to reset to the default values excluding those values that currently in the second listbox.

The entire code is below for reference, but it's the sub ClearFilter_Click that I am struggling with.

[Code] ....