Is it possible to analyse text in Excel in cells in a column and use true/false outcomes to select a value from a vector (or column as I suppose you experts call it) and to output that value to a different cell ? eg A1 has text value green, B1 has numerical value n1, C1 acquires value n1 from B1, D1 acquires value 0 or blank; A2 has text value blue, B2 has value n2, C2 acquires 0 or blank, D2 acquires value n2 from B2; A3 has text value green. B3 has value n3, C3 acquires value n3 from B3, D3 acquires value zero or blank; ... Sorry I tried to lay this out as a table, but the forum text editor didn't like it - great for a forum on Excel, eh? Anyway I hope this makes sense and that there is a nice simple solution that I've just overlooked. Thanks, Andrew

Hi Andrew and welcome to the forum.
You can always attach an example workbook to the thread?


If I understand correctly, you're saying that if column A is "Green" then column C should pick up the value from column B, but if column A is "Blue" then column D should pick up the value from column B?

In cell C1 put this formula:
In cell D1 put this formula:

__________________
Colin

RAD Excel Blog

Dear Colin Many thanks both for reply and for amazing swiftness. Silly of me not to think of attaching a file. That's pretty much what I want but when I tried that form of IF statement in Office 2010 it didn't work. Now it may well be my lack of experience of 2010, or with IF statements, or just plain carelessness, so I'll have another try and let you know how it goes. Thanks again, Andrew

Dear Coliin Worked like a charm. Many, many thanks - I might even be able to finish my tax in time. Comment: You have to put in the full text of the column being used to sort on, it doesn't seem to like using only the first letter or word, but that's ok as long as it's not too long, though it gets a bit tedious when there are many diffferent texts to deal with. Now if only one could add several text choices into one column - e.g. IF either "yellow" or "blue" then green column acquires the value of the associated cell - but one can just add the columns afterwards, so it's no great hardship. Saved me having to resurrect my rusty BASIC - many thanks again Best wishes, andrew

Hi Andrew,It's possible to do partial matches too. For example, to see if A1 contains the word "blue" you can use:
You can then extend it for multiple words. To see if A1 contains the words "blue" or "yellow" you can use:
To see if a cell contains "red", "blue" or "orange" you can use:
If you want to put the list of colours in a range and reference them (rather than using an array constant {.....}) then you have to use something slightly different - let me know if you want to do this and I'll show you how.

Note - SEARCH() is not case sensitive. If you want the formula to be case sensitive then use FIND() instead of SEARCH().

__________________
Colin

RAD Excel Blog

Wow ! and yes please, I'd love to know - since I've started this, I may as well get up to speed on every aspect for next time, because I can see it coming. Thanks, Andrew

Sure,

If you put the list of colours, say red, blue and orange into cells J1:J3, two options would be:

(1) Use the same formula as above, but with the array constant replaced by the range and enter it as an array formula. To do this, when you type the formula into the formula bar, finish the entry with CTRL+SHIFT+ENTER instead of just ENTER. If done correctly then Excel will automatically surround the formula with braces { } (you can't manually enter these yourself).
(2)
Use a variation such as this one (which can be entered as a normal formula).

__________________
Colin

RAD Excel Blog

Dear Colin Thanks again. My apologies for not replying before -among other distractions, Microsoft is suddenly trying to claim that my W7 is illegitimate and has blocked the registration key - I bought this W7 from them, so I can understand their concern... Anyway my thanks for all the work you have put into clarifying what I probably ought to have been able to work out for myself, and at least I am better informed if no less an idiot. Best wishes, Andrew