If isnumber search problem

In J50, I dont want Excel to return the value "CML5" like it does in J44. When "3x5" and "logo" are both present, I want the "CML5" to populate in J44.
How do i write it "and...."?

Hi Bryden,

I took the test for LOGO out of each individual test, and put it at the beginning. Is this what you want - it returns BLANK in J50 and CML5 in J44.

Please Login or Register  to view this content.

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

Either change your search string format to "*LOGO*2X3*", etc. Or if "LOGO" could appear after "2X3" and should still be included as a valid result then try AND(ISNUMBER(..)) with your existing array constant.

Originally Posted by newdoverman

I eliminated the array constants in favour of IF(AND( so that both criteria had to be present for each result. This eliminated the value in J50 as you wanted and maintained the rest.

Ron, see my comment in post #5,

Formula:

Please Login or Register  to view this content.

which is effectively the same as your formula also works, as should

Formula:

Please Login or Register  to view this content.

if, as appears to be the case, the word "LOGO" always comes in front of what appears to be the product size.

Reducing the number of functions makes the formula easier to manage.

Also, for what it's worth, wildcard characters serve no purpose in the criteria of the search function unless used in the middle of a string, as with the second example above. 3 of us didn't see that sooner

bryden2008, does the sample sheet contain the whole list of criteria, or is there more to add in later?

If your list is going to be much bigger, I would consider looking at ways to extract the product size from the string to use as search criteria for a lookup table.

If you read my post properly, there was no assumption, that was a secondary suggestion that should work if the real data is consistent with the sample, hence asking the OP if there is more to add to the list.

I also chose to retain the OP's original working method, which is why my first suggestion retained their array constant by wrapping their existing array search with the AND() function rather than removing it and adding many more functions than needed.

If you compare your formula to this one, which is the full version of what I was suggesting, you will see that this bears a much closer resemblance to the original formula and uses only 4 extra functions, where as yours uses something like 12 extra functions (I didn't count them but it should be 12 anyway).

Formula:

Please Login or Register  to view this content.

Originally Posted by Jacc

David, what are they curly brackets for?

You're right Jacc, I should have removed the curly brackets. They are redundant once you are not looking for two words! (eg LOGO and 2X3).

My reading of the formula was that Bryden wanted LOGO AND 2X3 etc. So instead of using multiple tests using AND, I tested for LOGO, then if this was found, looked for 2X3 etc.

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

Hi David,
That worked.
Why did that work?

So here is a new problem -
For this formula in Z61 - Excel recognizes the word "Locker", but not "Drum" in Z62 and Z63

Why?

It will not recognise "Can", "Stand" or "Z-Bar" either.

If you're going to use arrays as search criteria then you need to wrap ISNUMBER with AND if all criteria should be TRUE, or wrap ISNUMBER with OR if you only need one to be TRUE.

See my previous replies which show how to apply this method to your original problem, using the same method, simply change AND to OR for the new problem.

Without using AND / OR the formula only checks the first criteria in the array, the rest of it is ignored.

That worked Jason, thank you!