Excel – If an Excel row contains particular words, return that row, highlight that row, or give Y/N for that row

findmicrosoft excelworksheet-function

I have a sheet containing thousands of cells in a column. These cell contains names of different securities.

Some names of these securities contains a particular string such as "C/O".

How can I use a formula that will return the rows that have these characters?

EG.  
S&P 500 C/O 30/03/12 1380  
MICROSOFT C/O 19/05/12 32  
QUICKSILVER C/O 17/03/12 9

There is no consistency (can't use left/right/mid formula).

Requirement : I want the formula which will return "C/O" in the other cell if this particular cell contains it.

Best Answer

Use conditional formatting option Use a formula to determine which cells to format with the following formula:

=IFERROR(FIND("C/O",A1),0)>0

Whenever your cell has C/O in it, it will return a value greater than 0, and you can have any formating applied (e.g. change fill color). When C/O is not present, it will remain formatted normally.

Conditional Find 2

Conditional Find

The FIND function looks for C/O and returns it starting point, which is 1 or greater. If C/O is not present it returns a #Value error. So, this uses IFERROR to return 0 for all error conditions (i.e. when C/O is not present).

EDIT: Using a reference cell for your search text (i.e. C/O in cell C1) will allow you to change your search string and highlight other cells based upon that. Note in the conditional formating window, you'll need to use an absolute reference to make sure that regardless of the evaluated cell, it always looks to $C$1. For example, you can have it find S&P:

enter image description here

Related Solutions

Excel – Lookups targeting merged cells – only returning value for first row

I don't think there is any easy way to do this with existing Excel formulas. The problem is two-fold. First, AFAIK there is no Excel function that will tell you if a cell is part of a merged range. Second, the value shown in a merged ragne is actually only in the first cell of the merged range.

If you are willing to use VBA to create a custom function, this can be done fairly easily with a combination of the Match function and the fact that Range objects know if they are part of a merged range.

Public Function VLookupMerge(lookup_value As Variant, table_array As Range, col_index As Long) As Variant
   Dim sMatchFormula As String
   Dim row_index As Variant
   Dim r As Range

   sMatchFormula = "=MATCH(" & lookup_value _
                        & "," & table_array.Columns(1).Address(External:=True) _
                        & ",0)"
   row_index = Application.Evaluate(sMatchFormula)

   If TypeName(row_index) = "Error" Then
      VLookupMerge = row_index
   Else
      Set r = table_array.Cells(row_index, col_index)
      VLookupMerge = r.MergeArea.Range("A1")
      Set r = Nothing
   End If

End Function

Excel: How to return the last Row Number that a contains a specific string

To find earliest instance, mentioned below is the formula:-

=LOOKUP(2,1/(A:A=A2),ROW(A:A))

For your better understanding, below is the screenshot

enter image description here

Best Answer

Related Solutions

Excel – Lookups targeting merged cells – only returning value for first row

Excel: How to return the last Row Number that a contains a specific string

Related Question