Look-up a value within an interval

[Christine]

I would be very grateful if someone could provide me with a formula that will
look up the closest minimum and maximum based on a value and bring back the
contents of a cell to the right. In the example below, I want the formula to
look up 9,923, within the ARC intervals, and bring back Result 2.

ARC 9,923 Formula will bring back Result 2

A B C D
Minimum Maximum
1 ARC 0 10,000 Result 1
2 ARC 4,000 10,000 Result 2
3 ARC 10,000 9,999,999 Result 3
4 ARD 0 10,000 Result 4
5 ARD 10,000 9,999,999 Result 5
6 DCC 0 15,000 Result 6
7 DCC 4,000 9,999,999 Result 7
8 DCC 15,000 Result 8
9 DCD 0 9,999,999 Result 9

Thank you in advance for your help.

Chris

 

[Tom Hutchins]

I'm sure there are more elegant solutions, but try this...

Assuming your lookup data is A110, the type of interval (ARC) to look up
is in A15, and the value to look up is in A16:

In the cell where you want to return the answer, enter

=INDIRECT(ADDRESS(SUMPRODUCT(--($A$2:$A$10=A15),--($B$2:$B$10<=A16),--($C$2:$C$10>=A16),ROW($A$2:$A$10)),4))

Adjust the cell references as needed.

Hope this helps,

Hutch

 

[Ashish Mathur]

Hi,

The best way to do this is by having the minimum and maximum values in 2
different columns (say A1:A10 and B1:B10). Also in a third column (say
C1:C10) enter numbers starting with 1 I.e. 1,2,3....

Now use the SUMPRODUCT() formula

=sumproduct((A1:A10<=9923)*(B1:B10>=9923),C1;C10)

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Christine]

Thank you, Tom. However, the result that comes back is Result 4 when it
should be Result 2. I've also tried it with different references, but the
result of the Address portion of the formula comes back with a reference a
few rows below what it should be. Would you have any idea why that would be
happening?

 

[Christine]

Thank you, Ashish. However, this brings back a result of 0.

 

[Roger Govier]

Hi Christine

Tom's formula returns the correct result for me.
The only way I can see it returning a value 2 rows below the value required,
is if your data starts in Row 4, not in row 2 as Tom assumed.
Change $2 to $4 throughout the formula, and see what that does.

 

[Christine]

I don't know where my formula is wrong, because I still get the wrong result.
Please see below, where the data is from cells A110, "ARC" in cell A15 and
9923 in cell A16.

Minimum Maximum
ARC 0 10,000 Result 1
ARC 4,000 10,000 Result 2
ARC 10,000 9,999,999 Result 3
ARD 0 10,000 Result 4
ARD 10,000 9,999,999 Result 5
DCC 0 15,000 Result 6
DCC 4,000 9,999,999 Result 7
DCC 15,000 Result 8
DCD 0 9,999,999 Result 9


ARC
9923 Result 4

=INDIRECT(ADDRESS(SUMPRODUCT(--($A$2:$A$10=A15),--($B$2:$B$10<=A16),
--($C$2:$C$10>=A16),ROW($A$2:$A$10)),4))

 

[Christine]

Actually, it seems to work for some entries but not others. I think the
formula does work.

 

[Tom Hutchins]

Your sample data is inconsistent. For example, ARC 9923 falls into your
Result 1 range and also into your Result 2 range. The DCC ranges don't make
sense. I adjusted the data to the following when I was creating the formula:

Minimum Maximum
ARC 0 4,000 Result 1
ARC 4,000 10,000 Result 2
ARC 10,000 9,999,999 Result 3
ARD 0 10,000 Result 4
ARD 10,000 9,999,999 Result 5
DCC 0 4,000 Result 6
DCC 4,000 15,000 Result 7
DCC 15,000 9,999,999 Result 8
DCD 0 9,999,999 Result 9

Could that be why you are getting erratic results?

Hutch

 

[Christine]

Unfortunately, there are other factors involved, and the limits cannot be
changed. It's also a matter of tiering. For example, if the building is
less than 5 years' old and the amount falls in between 4000 and 10000, it
gets tiered at level A; if the building is less than 5 years' old but the
amount falls between 1000 and 10000, it gets tiered at level B, etc. with
additional factors. What I want to do is concatenate all the factors and
bring back the appropriate tiering, and that means finding the closest
minimum and maximum based on the amount and the subject (e.g. ARC, DCC).

 

[Ashish Mathur]

Hi,

Try this

=sumproduct((A1:A10<9923)*(B1:B10>9923),C1;C10)

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Tom Hutchins]

You are getting Result4 for ARC 9923 because it falls into the ranges for
both Result1 and Result2. The SUMPRODUCT part of the formula returns the row
number of the solution. Because your min/max ranges are inconsistent, it is
returning 2 and 3, then adding them to get 5. Row 5 has Return4.

Hutch

 

[Christine]

Thank you, Tom.

You are getting Result4 for ARC 9923 because it falls into the ranges for
both Result1 and Result2. The SUMPRODUCT part of the formula returns the row
number of the solution. Because your min/max ranges are inconsistent, it is
returning 2 and 3, then adding them to get 5. Row 5 has Return4.

Hutch