Struggling with wildcards in IF function

You can't use wildcards in logical tests, you would need to use

Formula:

Please Login or Register  to view this content.

for a result anywhere in the cell, or

Formula:

Please Login or Register  to view this content.

to find an exact starting position. (=2 at the end or the formula means starts at the second character, so the same as a single "?" wildcard character).

You might find your task easier if you look into VLOOKUP.

Thanks for the reply. I've realised what I'm trying to do is actually not possible in Excel, so we're going to look into another solution. Thank you again though, I've definitely learned something today

Originally Posted by xgavversx

I've realised what I'm trying to do is actually not possible in Excel, so we're going to look into another solution.

Which part of it do you think is not possible?

Based on your question and sample, I would say it just needs a little research. Having multiple choices in a single cell is not ideal, but you can work with it.

If you're finding that you have too many combinations given the variations of "01,02", "1,02", "01;2", etc. Then you might find that this helps.

Formula:

Please Login or Register  to view this content.

Not sure what you were trying to do with 'Service!$B2!<0' at the end of your formula, that would only pick up on negative numbers, which doesn't appear to relate to anything in your sheet.
I've left it the same in my suggestion, but think that it needs to be something else.

What I meant was it's not possible for me to do in Excel at my current level. There's a lot more I need to learn before I try and attempt something like this on my own.

'Service!$B2!<0'

The point of this was to find a response other than NULL. I figured seeing as a response in Col. B is a number, it is greater than 0.... I did leave the wrong formula in my example though, because that's a less than symbol! :S Originally I tried "<>NULL" but I guess Excel doesn't like that.

When I have time today I'm definitely going to take this away and research all these functions and how this formula works. Would the solution be easier for me if I insert 8 columns between Col. A and delimit Col. A with commas maybe? This was also something suggested to me. I don't know if this works in practice though, as this was suggested by someone who works with databases, not Excel. I was hoping to keep all the work to 1 column though.

Thank you for the knowledge and help

You almost had it right, it should have been <>"NULL" not "<>NULL"

The way you tried it is used in excel, but only with countif(s), sumif(s) or averageif(s).

What you need might not be possible for you in excel, but that's why we're here.

You can delimit column A, that is effectively what the formula does, to make things more 'uniform' it replaces ; with , and removes any zeros so that you are left with a comma delimited list if single digit numbers, which gives you less variations to try and check.

One point I will make in case you're not aware, as I get the impression this might be something you need to do. A formula can only show a result in the cell that it occupies, you can't "send" the result to another cell without a formula to override or restrict what users enter. That would need either data validation or vba depending on the requirements.

I think I found the solution, however there's probably junk I don't need in the statement.

I'll underline what I changed:

Formula:

Please Login or Register  to view this content.

For some reason, wrapping the 0 with double quotations in the second logic check of the AND statement seems to work. I now get TRUE1 and TRUE2 in the correct cells instead of TRUE1 and ERROR. I did some spot checks and it seems to do what I want it to and put ERROR where it needs to go.

Did you see the comment in my last post about <>"NULL"and "<>NULL" ?

I would advise being very careful with the use of "0" in a greater than, equal than, or less than situation. As with the above, it works when used correctly, but can be misleading.

For example "-1" is greater than 9999999999 (no "")

Excel stores numbers in 2 different ways, number format and text format. Any number in text format, no matter how small, will be greater than the largest number in a proper number format.

Numbers in text format are sorted 'alphabetically' not numerically, i.e. the number is read from left to right, so in order you would get 1, 11, 12, 123456, 2, 21, etc. Meaning "2" is greater than "123456"

As with anything, this can be very useful, but also cause problems when used incorrectly.

A web search for "Excel numbers stored as text" should return more information on this if it's not making sense, and no doubt explain it better than I can.