Tom's Guide | Tom's Hardware | Tom's Games
 |
 |
 |
I have two columns of information. Column A represents original customer names and Column B represents new/updated customer names. I need a formula that can check a text value in a cell in column B against the values in a range of text values in Column A and if the exact value does not exist, return the value of the cell in Column B that was being checked, else "not updated"
Place this formula in cell C1 and then drag it down for as many rows as you need:
=IF(A1=B1,"Not Updated",B1)
Michael J
0 
This formula won't work because the value in column B can be anywhere in column A not necessarily in the adjacent cell.
0
Oops, I missed that little detail. Yes that is a little more difficult, but very doable.
The first thing you will want to do is create a Named reference for the data in column A. The easiest way to do this is to select all the data in that column and then in the Name box (which is directly above cell A1) type a name to refer to that data. For this example, let's call it 'original'.
Now in cell C1 enter this formula:
=IF(ISNA(MATCH(B1,original)),B1,"Not Updated")You can now drag and copy that formula down to the last data row in column B.
Hope this helps.
Michael J
0
I am still having trouble. The formula is returning "Not Updated" for all the names regardless of whether a name exists in Column A for the cell in Column B that is being sought in Column A.
Example below shows cell reference in parenthesis (A1) (B1) (C1) Column A represents the original name, Column B represents the updated/new list and Column C is the formula result:
(A1)1-800-Bar None A Fincl Corp (B1) 1125 Sir Francis Drake Bouleva (C1) Not Updated
(A2) 1st United Services Credit Un (B2) 1185 Design (C2) Not Updated
(A3)24 Hour Fitness Usa Inc (B3) 1-800-Bar None A Fincl Corp (C3) Not Updated
(A4)24 Hour Fitness Worldwide Inc (B4)1st United Services Credit Un (C4) Not Updated
(A5) 2wire Inc (B5) 24 Hour Fitness Usa Inc (C5) Not Updated
(A6) 3dconnexion (B6) 24 Hour Fitness Worldwide Inc (C6) Not Updated
0
I get the same results if I do not create a named reference to the data in column A as I described above. Did you create the named reference exactly the same as it appears in the formula 'original'?
I input the data from your example and the results I got were that the data in B1 and B2 were repeated in C1 and C2. Cells C3-C5 showed "Not Updated". So it appears to work for me.
Michael J
0  |
 |
 |
This post is quite old and has been locked from receiving new replies. Please create a new posting instead.
| Ads by Google |
