ChetShannon
Board Regular
- Joined
- Jul 27, 2007
- Messages
- 133
- Office Version
- 365
- Platform
- Windows
I am trying to find a way to do a lookup based on the text contents of a cell and the target text is not regularly placed in the cell. For example the cell might have the word RED or BLUE or GREEN placed in any particular position in the cell and I want to have another cell (calling it the sister cell) return a value from a lookup table (in the same workbook) based on the first cell having RED, BLUE or GREEN in it. For example if the cell has RED in it then the sister cell would do a lookup and return 10.0 if it's RED, or 20 BLUE is in the cell or 30 if GREEN is in the cell somewhere.
A1 might be "THE SKY IS RED" and B1 would return 10 because A1 has RED somewhere in it. The letters RED could be anywhere in A1 though so using MID might not work since you don't where the text of RED will be. The next time A1 might be "BLUE IS THE SKY" and B1 would return 20 because BLUE was somewhere in A1.
I hope I am explaining this well enough. Thanks! Chet
A1 might be "THE SKY IS RED" and B1 would return 10 because A1 has RED somewhere in it. The letters RED could be anywhere in A1 though so using MID might not work since you don't where the text of RED will be. The next time A1 might be "BLUE IS THE SKY" and B1 would return 20 because BLUE was somewhere in A1.
I hope I am explaining this well enough. Thanks! Chet