Compare range of values

[westend9876]

I have a range of values in Row 1 Columns A to D that I call Range A. Also, another range of values in Row 1 Columns F-G that I call Range B.

This is what I need.
If any value between the low and high of Range A falls between the low and high of Range B then return "Yes" in Column I otherwise return "No".

I attached the sample worksheet. Thanks.

 

[NARAYANK991]

Hi ,

Will Range B be just 2 values ? If so , it can be simplified to :

=IF(SUMPRODUCT((A2:D2>=F2)*(A2:D2<=G2)),"Yes","No")

Narayan

 

[westend9876]

Hi Narayan. No Range B will have varying values. About 20% of the values in Range B will have 3 or more numbers. Thank you.

 

[NARAYANK991]

Hi ,

In that case , try this :

=IF(SUMPRODUCT((A2:D2>=MIN(F2:H2))*(A2:D2<=MAX(F2:H2))),"Yes","No")

Narayan

 

[westend9876]

Hi ,

In that case , try this :

=IF(SUMPRODUCT((A2:D2>=MIN(F2:H2))*(A2:D2<=MAX(F2:H2))),"Yes","No")

Narayan

Thank you very much. This worked perfectly.

 

[westend9876]

Hi Narayan. As a follow up I realized that I need to search through the ranges. For example:

I have a range of values in Columns A to D that I call Range A. Also, another range of values in Columns J-K that I call Range B.

This is what I need.

Go row by row in Range A and If any value between the low and high of the row in Range A falls between the low and high of ANY of the ranges in Range B then return "Yes" in Column F for that row and return the corresponding zone number in column G for that row. Otherwise return "No" in column F.

I attached a revised worksheet with a few sample manual results.

Thanks

 

[NARAYANK991]

Hi ,

Give me some time.

Narayan

 

[westend9876]

Hi ,

Give me some time.

Narayan

sure thanks

 

[NARAYANK991]

Hi ,

See your file now.

Narayan

 

[westend9876]

Hi Narayan. I will have multiple worksheets for each currency pair with the same Range formats as sheet EURUSD, but with different values. Will I have to define names as you did for all of these worksheets?

 

[NARAYANK991]

Hi ,

I just noticed that the formula is wrong.

Please wait till either I or someone else can upload a revised file.

Narayan

 

[NARAYANK991]

Hi ,

See the file now.

Narayan

 

[westend9876]

Hi ,

See the file now.

Narayan

Wow. You are really good. I have no idea how it works. I'll have to try and dissect the formulas later. Main thing is that is worked. Thank you kindly.

 

[Somendra Misra]

Hi westend9876,

Another alternate formula:

For getting Yes/No in Cell F3:
=IF(MAX(MMULT(--(($J$4:$J$13<=$A3:$D3))*--(($K$4:$K$13>=$A3:$D3)),TRANSPOSE(COLUMN($A3:$D3)^0))),"Yes","")

Here J4:J13 Lower Zone Value & K4:K13 has Upper Zone Value.

For getting zone number in G3:
=IF(F3="","",INDEX($I$4:$I$13,MATCH(TRUE,MMULT(--(($J$4:$J$13<=$A3:$D3))*--(($K$4:$K$13>=$A3:$D3)),TRANSPOSE(COLUMN($A3:$D3)^0))>0,0)))

Here I4:I13 is Zone number and Upper zone Number & Lower Zone number as above.

Please note that both the formulas are array formula so must be entered with Ctrl+Shift+Enter.

Regards,