Determining whether a value falls within a range

[919budda]

Hello all,

I have a challenge I've been thinking about lately - how to determine whether a value falls in between a range. The challenge is that the value and range can be alphanumeric. Example: Does A11 fall inbetween A10 and AZZ? (the answer is yes)

The solution can be formula or user defined function, and I need it to check two values (functional area and fund center) and both must be within the range of the same row.

I'm attaching a sample file which I hope clarifies my request. All help is appreciated.

 

[Luke M]

Hi, and welcome to the forum 919budda!

Formula in D12 would be:
=IF(SUMPRODUCT((ISNUMBER(SEARCH($C$4:$C$7,B12)))*($B$4:$B$7>=A12)*($A$4:$A$7<=A12)),"Yes","No")

The sumproduct builds 3 criteria arrays. First array checks if any of the Funds Centers match their patterns. It then check if the Function Area fits within the correct band. If it finds a row in Valid Ranges that meets all the criteria, returns Yes, otherwise No.

 

[919budda]

Thank you Luke, I appreciate the fast response. The formula doesn't check the ending range of the funds center though. I tried using

=IF(SUMPRODUCT(($C$4:$C$7<=B12)*($D$4:$D$7>=B12)*($B$4:$B$7>=A12)*($A$4:$A$7<=A12)),"Yes","No")

but it does not work if the ending fund center is blank. Any ideas how to overcome this?

 

[919budda]

I think I've got it.

=IF(SUMPRODUCT(($C$4:$C$7<=B12)*((ISBLANK($D$4:$D$7)+($D$4:$D$7>=B12)))*($B$4:$B$7>=A12)*($A$4:$A$7<=A12)),"Yes","No")

Thanks for the help.

 

[Luke M]

What do you want to happen if fund center is blank? Assuming it has to be filled in:
=IF(COUNTA(A12:B12)<>2,"Fill in blanks",IF(SUMPRODUCT(($C$4:$C$7<=B12)*($D$4:$D$7>=B12)*($B$4:$B$7>=A12)*($A$4:$A$7<=A12)),"Yes","No"))