Searching a range of cells using an -IF statement

[hissonrr]

Good day all,

Here is my question... Is it possible in excel to search a range of cells to find if a certain cells value meets a certain criteria in another range of cells.

For example I have the table below...

column C is just =(Perf Depth -10)
column D is just =(Perf Depth +10)

I am looking for some kind of formula for column A, and what I need it to do is take the corresponding cell in column E and see if that value falls within all the ranges of columns C and D.

For example looking at cell A2, does cell E2 (90) fall within any of the ranges in all of column C and D, so does 90 fall between 0-20, 40-60, 90-110... ect


let me know if anyone has any questions or solutions; and I can not use an embedded IF statemented because the range of actual cells will be to large. The table below is just an example.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Qualifier[/TD]
[TD]Perf Depth[/TD]
[TD]-10'[/TD]
[TD]+10'[/TD]
[TD]FIP[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]X[/TD]
[TD]10[/TD]
[TD]0[/TD]
[TD]20[/TD]
[TD]90[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]X[/TD]
[TD]50[/TD]
[TD]40[/TD]
[TD]60[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]X[/TD]
[TD]100[/TD]
[TD]90[/TD]
[TD]110[/TD]
[TD]110[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]X[/TD]
[TD]150[/TD]
[TD]140[/TD]
[TD]130[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]200[/TD]
[TD]190[/TD]
[TD]210[/TD]
[TD]230[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]X[/TD]
[TD]250[/TD]
[TD]240[/TD]
[TD]260[/TD]
[TD]240[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]X[/TD]
[TD]300[/TD]
[TD]290[/TD]
[TD]310[/TD]
[TD]250[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]X[/TD]
[TD]350[/TD]
[TD]340[/TD]
[TD]360[/TD]
[TD]300[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

Place this formula in cell A2 and copy down for to row 9:

=IF(COUNTIFS(C$2:C$9,"<=" & E2,D$2:D$9,">=" & E2)>0,"x","")

 

[hissonrr]

Place this formula in cell A2 and copy down for to row 9:

=IF(COUNTIFS(C$2:C$9,"<=" & E2,D$2:D$9,">=" & E2)>0,"x","")

Worked like a charm thanks.

I htin kI understand but please tell me if i am wrong just so I can learn.

Basically what you did was make a conditional statement that marked Column C and the low end of the range number and column D as the high end of the range number then set it so if it falls in the range it is a value of 1 then marked all 1's as an x and all the 0s as blank?

 

[Joe4]

It is simply going through each row, and counting all instances where the value in column E is greater than or equal to the value in column C, and less than or equal to the amount in column D.
So, if three rows met that condition, the COUNTIFS would return 3. We don't care how many times it meets the criteria, as long as it is greater than 0 (at least once).

See here for more details on COUNTIFS:
https://exceljet.net/excel-functions/excel-countifs-function
and here, which shows and explains an example more like yours:
https://www.extendoffice.com/documents/excel/2412-excel-count-cells-between-two-values.html