help with range matching. Is this possible?

[kp20]

Hello,

I'm trying to match specific numeric values on one tab against columns on another tab that depict many ranges of numeric values. Here is specifically what I need to do.

On tab one in column A I have specific numeric values throughout the entire column. On tab two I have a list of ranges, with the first value in a numeric range being in column A and the last value in a numeric range being in column B. The ranges are represented on multiple rows on tab two. For example, tab two cell a1 has a 5 and b1 has a #10 to represent range 5-10. Cell a2 has 15 and cell b2 has 23 to represent the range 15-23. ranges like this continue down the this sheet for hundreds of rows to represent hundreds of ranges.

I'm trying to find a way to systemically determine if any of the values from the first tab in column A fall within ANY of the ranges on the second tab. I cant use a vlookup because I only have the first and last numeric value in each range. Vlookup will, of course, miss anything that falls within the range since its not specifically noted on the range sheet.

Is there a function that will allow me to accomplish what I need to do? I've tried countifs and sumifs and I'm not having any luck.

Thanks.

[NickyC]

Hi
you can probably do this with the countifs function. If your first value to test is is in cell A1, your minimum range values are in sheet2 range A1:A10, and your maximum range values are in Sheet 2 range B1:B10, this formula will count the number of occasisons when the value of A1 falls between the mininums and maximums. Copy down for other values in column A

Please Login or Register  to view this content.