Formula to find numbers within several ranges

Hi,

I'm trying to figure out a way of flagging numbers in a spreadsheet that fall between various number ranges, i'm fairly new to these kind of formula so could do with a little help

What I have is a dump of all the external telephone calls made on our telephone system and what I want to find out is which of these calls could have been made internally between sites.

So I have several phone number ranges i'm looking for in this dump and would like an easy way of marking them. Currently I have been importing it into an Access DB and using the "Like" function to filter out all the numbers and then having to export it back to XL to do various calculations.

Is there a way I can do all this just within XL?

Thanks,

Kam

Can you upload example workbook?

There undoubtedly is a way, possibly with formulas, definitely with VBA. But you'd probably need to attach a dummy spreadsheet to get a more specific answer.

Hi,

Here is a sample sheet. the Code&Number column is the one i need to filter. So the range I would be looking to flag for example would be 111190050 to 111190099.

Thanks,

Kam

How output should look?

To be honest I dont really mind, might be easier if it put a true/false in the next column maybe?
I've tried with the IF statement but could not figure out how to ignore the country code at the begining or do the full range.

Thanks,

Kam

Something like this? (based on 9 digit numbers)

Hi zbor,

Thats exatly the kind of thing i'm looking for

How would I now add in other ranges of numbers? I have another 16 number ranges to filter on

Many thanks for your help.

Kam

Then you need slightly different approach...
I add new sheet Ranges where you can add or remove ranges...

Result will be number of ranges where number fall in...
If you prefere TRUE/FALSE output put at the end >0

See attached for aternative route, although it must be noted that the ranges in L:M must be kept in numerical order (lowest to highest).

Thank you both,so much you are both genius

This will save me a great deal of heartache and time.

Thanks again.

Kam

@brokenbiscuits: good approach..

Only note that for TRUE/FALSE result you dont need IF function... I.e. this will work too:

=VALUE(RIGHT(F2;9))<=INDEX(M:M;MATCH(VALUE(RIGHT(F2;9));L:L;1);1)

(use , instead of ; )