Forum Discussion
Multiple IF Statement
I'm trying to use multiple IF(SEARCH) functions to find text within a specific column to return other specific text. I have attached a sample. I would like to write an IF statement that searches for "Black" in column A and returns "Black" in column B, searches for "White" in column A and returns "White" in column "B" and so on.
8 Replies
IF( SEARCH... practically never works. IF() evaluates condition, depends on it returns TRUE or FALSE calculate the result. But if we it returns an error, entire IF() returns error.
Usual combination for such cases is to use IF( ISNUMBER( SEARCH( ...
ISNUMBER returns TRUE if text is found and its position is returned. If error it has another (not number) type, thus ISNUBER returns FALSE.
Another point it's not clear would you like return color as very first word of the text or it could be at any place of the text. If former it's easier, just find position of first space and LEFT() text on it.
If the latest I'd create in any place of the workbook the list of words to be found, like
with that formula could be
=INDEX($D$2:$G$2, MMULT(--ISNUMBER(SEARCH($D$2:$G$2,[@[Item Description]])), TRANSPOSE($D$1:$G$1)))if you did a query from a system you should include the sku number because the sku number might identify the color of the product and base your formula on said sku number instead of re-inventing the wheel
- Thank you for your response. Unfortunately in my actual data, the SKU numbers are random in relation to the item description.
https://www.lightspeedhq.com/blog/sku-numbers/#:~:text=A%20SKU%20number%20is%20an%20alphanumeric%20code%20that,are%20critical%20for%20managing%20inventory%20and%20maximising%20sales.
Maybe your example is not really representative of the real data you'll be working with, but IF in fact the color in question is always the first word in the text being searched, the formula below works just fine. See the attached file (i changed the name, but it's your file with my solution)
=LEFT([@[Item Description]],FIND(" ",[@[Item Description]])-1)
Otherwise, using a series of nested IFs can get really complicated. You might try the function IFS instead.
mathetes Thank you for your response. This ended up not being a great representation - the key text identifier is not always the first word. I tried using IFS, and still got errors. I'm not great at nesting but that may be my only option.
- So create a more representative sample for us to work with.
