So Many Numbers, Too Little Knowledge. What to do with more than 7 "IF,OR" statements?

[brokerbreaker]

I have looked through forum after forum and I have come to the conclusion that I need something that would combine IF and "In between" functions hundreds of times. I have a list of numbers and I am trying to see if each number falls into any of a list of ranges...for example these are 10 of my ranges (A:B), with 3 of my numbers being tested (C), and what should happen in the D column. Even in this example each number would need to be tested 10 times with YES occurring if it happens to fall within any one of the ranges.

.....A............B..........C.......D
1419.579 1422.421 1382 YES
1413.585 1416.415 1270 NO
1381.617 1384.383 1421 YES
1375.623 1378.377
1344.654 1347.346
1332.666 1335.334
1308.69 1311.31
1302.696 1305.304
1272.726 1275.274
1266.732 1269.268

I don't need to know which range a random number falls in, but I need to know if the number is included in any of the ranges. I thought an IF with a MEDIAN statement would do it with the formula...

=if(or(C1=MEDIAN(A1:B1),C1=MEDIAN(A2:B2),"Yes","No")

The idea behind this formula works, but I have about 800 ranges and about 2000 numbers to test whether or not they exist in any of the ranges and working with excel I know there has got to be an easier way.

[NBVC]

Try:

=IF(COUNTIFS($A$2:$A$11,"<="&C2,$B$2:$B$11,">="&C2),"YES","NO")

adjusting ranges to suit... and copy down.

[brokenbiscuits]

=if(sumproduct(--($a$1:$a$10<c1)*--($b$1:$b$10>c1))>0,"yes","no")

[ben_hensel]

Try this:

Please Login or Register  to view this content.

Well, that depends on the ranges being sorted in descending order.