| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 100802 | 2009-06-21 01:20:00 | XL | B.M. (505) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 784173 | 2009-06-21 01:20:00 | I’ve done this many times but can’t remember how. :groan: Lets say in cell A1 we have the letter A In Cell B1:B4 we have the letters A,B,C,D respectively. Ok I want to put a formula in cell C1 that returns 10 if the letters A-D appears within the range B1:B4 and 0 otherwise. So, a letter A or B or C or D entered in A1 would return 10 and any other letter zero. I thought =IF(A1=$B$1:$B$4,10,0) would do it but no. Ctrl+Shift+Enter to give {IF(A1=$B1:$B4,10,0)} doesn’t do it either. I don’t believe I can’t do something so simple. Grrrrrrr. :groan: |
B.M. (505) | ||
| 784174 | 2009-06-21 03:59:00 | That works in Openoffice. Don't know why not in Excel. Upper & lower case? |
bob_doe_nz (92) | ||
| 784175 | 2009-06-21 04:40:00 | That works in Openoffice. Don't know why not in Excel. Upper & lower case? bob_doe_nz , did you try entering anything other than a in cell a1? I'm guessing you didn't. Here's an awkward way of doing it. =IF(ISERROR($A$1=VLOOKUP($A$1,$B$1:$B$4,1,FALSE)), 0,10) |
the_bogan (9949) | ||
| 784176 | 2009-06-21 05:48:00 | Yes, it will return 10 if you put an "A" in A1 but "0" for any other letter. I'm tearing my hair out here because it's actually very simple. I'm wondering if I used an =if(or(……………………….. to do it previously??????????? That's one way The Bogan and I'll use that if I can't find the simple way.:thumbs: |
B.M. (505) | ||
| 784177 | 2009-06-21 06:38:00 | Ahhhh {=if(or(a1=b1:b4),10,0)} works! :D (Don't forget the ctrl+shift+enter) | B.M. (505) | ||
| 1 | |||||