complex IF ??

**seraphin** · 02-28-2013, 08:05 PM

Hi, all
A newbie using "advanced Excel" functions and in need of help with the following task. Any info will be appreciated. Great many thanks.
I have three columns of number and one column of ID

A(ID) B(daily income) C(start) D(end)
peter 123 120 130
john 110 245 260
mary 345 330 340
judy 234 400 420
vicky 533 520 550
. . . .
. . . .
. . . .
. . . .

I need to identify all of those people, if any, whose daily income falls within any of the predefined income range.
For example, peter's income would be the first to be compared with ALL the ranges that are defined by column C and D.
In this case, because peter's income falls within the first bracket, peter is chosen.
Then john's income is compared with all the ranges defined in column C and D. Unfortunately, his income falls OUTSIDE
the range of the first bracket so john is not chosen. And so on and so forth.
In the example above, only peter and vicky are chosen. john, mary and judy do not have the "right" amount of income (not falling in any of the predefined range) and will not be selected.

I've tried a couple IF commands (actually I didn't really know what I was doing), but none gets me the right results (just by eyeballing the first 100 rows of the table).

Any help in constructing the right command will be greatly appreciated. Thank you

**alansidman** · 02-28-2013, 08:09 PM

In column E

IF(and(B1>C1,B1<D1),"Select","Not Selected")

**seraphin** · 02-28-2013, 08:20 PM

I've tried a similar command, but I found that in the example above, if we were to use this command,
john's income will only be compared to C2 and D2 bracket (245-260) and won't be compared to any other income brackets.
I need to compare each person's income with ALL different brackets in column C and D before I make a call (whether to include or exclude the person)

**alansidman** · 02-28-2013, 08:53 PM

How big is your recordset?

**seraphin** · 02-28-2013, 10:45 PM

unfortunately, it's BIG.
1011 IDs
7000 brackets,
although I just realized many brackets are duplicates (which I will remove). Still I'd say, brackets are in the range of 2-3000s.

**SDCh** · 02-28-2013, 11:38 PM

You want to check the income with range from 1st row till the end with condition :
if "Select" then stop checking and show the result else continue until end of row?

If that the case, you need vba.
See the attachment

**seraphin** · 03-01-2013, 12:30 AM

In honesty, the example I used in my posting is way simpler than the real case I am working on. In the actual case (not about income, nor bracket) some "brackets" overlap in their ranges they cover. Not only do I need to pick the individuals whose "income" falls within the range of one of the brackets but also identify all the "brackets" those selected individuals' "income" fall in. I hope I am not confusing the readers.