If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Using a grouping of cells to check one cell against another...
Hello,
There is a group of 56 cells that contain 3 letter groups (i.e. DCM, DCP, DCE, NTP). There is a column of cells that are listed in the following format (DCELNN010101). I want to find a way to use an IF statement to say: IF ("DCELNN010101" contains "Any of the 56 3 letter groups" then return TRUE, FALSE) I've been using =IF(ISNUMBER(FIND("DCE",L!B99)),"TRUE", "FALSE") with mild success. The problem is that I'm limited to using just one of the 3 letter groups (DCE) at a time. So with that logic I would have to create 56 formulas for each cell that I wanted to perform this on. No thanks. I want the formula to check the entire group of 56 groups and return whether that cell contains any one of the 3 letters. Thanks! |
#2
|
|||
|
|||
Hi
i'm assuming that your 56 3 letter codes are listed somewhere (sheet2!A1:A56) and i'm hoping that these three letters are to be found at the START of your other cells (as in your example), if so you can use the following formula =IF(ISNA(VLOOKUP(LEFT(D3,3),Sheet2!A1:A56,1,0)),FA LSE,TRUE) Cheers JulieD "deucer" wrote in message ... Hello, There is a group of 56 cells that contain 3 letter groups (i.e. DCM, DCP, DCE, NTP). There is a column of cells that are listed in the following format (DCELNN010101). I want to find a way to use an IF statement to say: IF ("DCELNN010101" contains "Any of the 56 3 letter groups" then return TRUE, FALSE) I've been using =IF(ISNUMBER(FIND("DCE",L!B99)),"TRUE", "FALSE") with mild success. The problem is that I'm limited to using just one of the 3 letter groups (DCE) at a time. So with that logic I would have to create 56 formulas for each cell that I wanted to perform this on. No thanks. I want the formula to check the entire group of 56 groups and return whether that cell contains any one of the 3 letters. Thanks! |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
changing cells depending on another cell. | Skills | Worksheet Functions | 3 | October 3rd, 2004 06:35 AM |
cell borders change in adjacent cells | Lisa | Tables | 0 | June 21st, 2004 11:58 PM |
Please help..activating array formulas in a range of cells without going cell by cell | Karenna | Worksheet Functions | 3 | March 1st, 2004 03:45 AM |
HELP! Return data if cell in row contains data | dee | Worksheet Functions | 13 | November 10th, 2003 06:48 PM |
Convert a Cell Reference to Text | Chuck Buker | Worksheet Functions | 6 | September 22nd, 2003 05:04 PM |