You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
I want to be able to instruct Excel to find a word in a cell, for example A518 in a cell, and then give that a completely different description in an other cell.
Let's say cell C2 has the word A518 in amongst a sentence, I want to find it, and then use it as reference so that excel then shows the word Red in cell E3. However cell C2 may have A519 or M6 or B2157 or A1234, there are 20 possibilities, and they will have a different reference in cell E3, blue, green, black, mauve etc
Please help
Give =ISNUMBER(SEARCH("A518",C2)) a go. That'll return TRUE or FALSE, you can then use that as part of the rest of your formula or for your conditional formatting.
Of course you could put the A518 bit in a cell and just use the cell ref for that bit too.
=INDEX({"Red","Blue","Green","Black","Mauve",...}, MATCH(TRUE, ISNUMBER(SEARCH({"A518","A519","M6","B2157","A1234",...}, C2)), 0))
Replace the ellipsis (...) with the remaining possibilities and their corresponding descriptions.
This formula uses the SEARCH function to find each possibility within the text in cell C2. The ISNUMBER function is used to check if a match is found, returning an array of TRUE or FALSE values. The MATCH function is then used to find the position of the first TRUE value in the array. Finally, the INDEX function retrieves the corresponding description based on the position found by MATCH.
Make sure to adjust the array of possibilities and their descriptions in the formula to match your specific case.
=IF(ISNUMBER(FIND("A518", C2)), "Red",
IF(ISNUMBER(FIND("A519", C2)), "Blue",
IF(ISNUMBER(FIND("M6", C2)), "Green",
IF(ISNUMBER(FIND("B2157", C2)), "Black",
IF(ISNUMBER(FIND("A1234", C2)), "Mauve",
"Other"
)))))
ChatGPT is great at writing excel formulas. I just cut and paste your text into its search. So far it's always got it right (I've not plugged this one into Excel to check)
If you have 20 possibilities, then adding them to any formula would be messy. I’d add an array of the combinations in a separate worksheet and use XLOOKUP with wildcard match. Example below:
RM.