Multiple true statements but need one to override the other

example workbook for forum.xlsxHi all, I have only been writing formulas for excel for a day and i'm struggling with an issue that i hope someone can help me with here. I have a formula iv'e put together which searches for key words in two cells and returns values based on the true results. The problem i have run into is when more than two keywords are true in either cell i get both results in the target cell rather than one, and i need only one. e.g if B2 or G2 contain the word "edging" then the value '40' is returned, if "oak" is in either of these cells then '2*E2' is returned, rounded up to the nearest 100 (for this eg 100), if both of these words exist in either cells then the result is '10040', but i need the formula to preference "edging" over "oak" and return only '40'.

here is the formula as it stands, it is a work in progress, it is supposed to add extra waste material for a cutting sheet.

=CONCATENATE(IF(SUMPRODUCT(--(ISNUMBER(SEARCH({"s/wood","softwood"},G4&B4))))>0,((ROUNDUP(2*E4,-2))),""),IF(SUMPRODUCT(--(ISNUMBER(SEARCH({"poplar","oak","sapele","walnut","ash","beech","birch","cherry","maple","iroko","cedar","balsa","olive"},G4&B4))))>0,((ROUNDUP(2*E4,-2))),""),IF(ISNUMBER(SEARCH("solid surface",G4)),(10),("")),IF(ISNUMBER(SEARCH("corian",G4)),(10),("")),IF(SUMPRODUCT(--(ISNUMBER(SEARCH({"edging","lipping"},B4&G4))))>0,"40",""),IF(SUMPRODUCT(--(ISNUMBER(SEARCH({"balance","veneer","laminate"},B4&G4))))>0,"20",""),IF(SUMPRODUCT(--(ISNUMBER(SEARCH({">"},G4))))>0,((ROUNDUP(2*E4,-2))),""),IF(SUMPRODUCT(--(ISNUMBER(SEARCH({"<"},G4))))>0,"100",""))

Hi and welcome to the forum!

Obviously to dissect that formula out of context (i.e. without seeing the actual workbook) will be a thankless and near-impossible task.

Kindly upload one, replacing confidential information with dummy data if necessary.

Regards

Thanks.

And would you be amenable to receiving a completely new solution, if that turned out to be possible, or would you prefer to keep your existing solution and have it amended so that it works?

I only say that as sometimes it's easier to tackle a problem from first steps than to try to dissect someone else's (in this case rather long) formula.

If you are open to that possibilty, could you confirm which of the values in your sheet are correct, and, if not, what they should be (you say that rows 7 and 8 are an issue, though I've no idea what the answer should be).

Regards

Thanks, so based on that, could you let me know which of your current entries in column K is correct? And, if not, what it should be?

Regards

Originally Posted by matt318

"softwood" or "s/wood" in either column B or G = 2x the material width (Column E) rounded up to the nearest 100, for additional length only.

"ash", "oak", "sapele", "softwood" or other wood types in column G = 2x the material width (Column E) rounded up to the nearest 100, for additional length only.

Great. Thanks. And can you just clarify why there are separate clauses in your current formula for each of these searches, when they appear to have an identical output?

Regards

matt, it seems like you are familiar with setting up a basic IF function such as =IF(A2="oak",40,"")
You have been using CONCATENATE here to combine IF functions such as =CONCATENATE(IF(A2="oak",40,""),IF(B2="edging",100,""))
However, a more common way of doing it is to nest the IF functions like this: =(IF(A2="oak",40,IF(B2="edging",100,"")))
When using nested IF functions you can set the priority by the order in which they appear. In the above example the A2="oak" condition has priority. In other words, if A2="oak" turns out to be true then you will get 40 and Excel won't bother to look at the next IF (B2="edging").


In my attached sheet I have been using Alt + Enter to get line breaks in the formula and some extra spaces to line things up, makes it easier to read. If grab the lower lip of the formula bar and pull down you will be able to see the whole thing.

I have also deleted a few unnecessary parathesis. I have also deleted a few >0 as they are not really necessary. Excel considers any number that is not 0 to be TRUE in an IF function. I consolidated some searches for material as the search was done in the same cells producing the same result. However, if you want some sort of priority between them then you may want to split them up again.

I think that there used to be a limit of 7 nested if's in Excel 2003 and older but from Excel 2007 and newer it's 64. However, if your heading down the road of lot's of nested if's you should reconsider your whole setup. The setup you have is not a concern if you ask me.

I fixed the other formula too. On another tab I converted it to an Excel Table. This feature can use headers instead of cell addresses in formulas (which I opted not too use). The reason I did convert it is that it expands all your formula and formatting automatically if new data is added anywhere in the row below the last row, no need to copy down to row 500 "just in case".

Glad to hear it works! :)
I made an attempt of making it more dynamic here, new types of wood can easily be added but if you want a new resulting value it will still require some work from you (creating a new table).

Making a proper Lookup setup with the possibility to easily change the resulting value for different kinds of wood will require running array formulas and the formula will be a lot bigger, as far as I can see. A UDF would be an option ofcourse.

I added a space between the cells to be searched to prevent the risk of "creating" words that don't exist. Hmm... now that I think about it that could go either way...:)

I have to say that apart from the CONCATENATE setup I am pretty impressed with the original SUMPRODUCT/SEARCH concept, it's hard to improve. :)