Find a value in a matrix using multiple criteria and greater than/less than

Joecam

Member
Joined
May 22, 2014
Messages
41
Reaction score
0
Points
6
Excel Version(s)
365
Hi all, hoping someone might be able to help me out with this formula. What I have is a table like the one below. I can amend the values in the table (particularly the last column) to make the formula work.

ABCDE
1
40%35%30%
2$250,000
0.5%1.5%2.5%<25%
3$250,0001.0%2.0%3.0%>25%
4$500,0001.0%2.0%3.0%<15%
5$500,0001.5%2.5%3.5%>15%
6$1,000,0001.0%2.0%3.0%<10%
7$1,000,0002.0%3.0%4.0%>10%

I'm trying match the first column, first row and last column to specific cells, however, none of the numbers that I'm trying to match are exact matches. For example, one set of data looks like this:

CDE
14$1,962,939.8416.8%23.65%

So I need to recognize that cell C14 is equal to or greater than 1,000,000, Cell D14 is less than 30%, and cell E14 is greater than 10%, therefore the formula should return the value shown in cell D7.

Could someone help?
 
Try:

=INDEX(OFFSET(INDEX($B$2:$D$7,MATCH(C14,$A$2:$A$7),0),-1,,2,),MATCH(E14,OFFSET(INDEX($F$2:$F$7,MATCH(C14,$A$2:$A$7),0),-1,,2,)),MATCH(D14,$B$1:$D$1,-1))
 
That works except when D14 is over 40% & when E14 is less than the corresponding values in E2:E7.

D14 I can just handle easily with an IFERROR at the front of the statement, since if the value in D14 is over 40 the formula should always return a 0. So I modified the formula to look like this:
=IFERROR(INDEX(OFFSET(INDEX($D$4:$F$9,MATCH(C13,$C$4:$C$9),0),-1,,2,),MATCH(E13,OFFSET(INDEX($G$4:$G$9,MATCH(C13,$C$4:$C$9),0),-1,,2,)),MATCH(D13,$D$3:$F$3,-1)),0)

I don't understand the formula enough to make the appropriate changes to correct the column E issue though.
 
Nevermind, simply changing the values in cells E2, E4 & E6 to 0% resolved the column E issue. Thank you for the solution!
 
Shoot... I forgot to mention that I had added column F (F2:F7) to list column E as 0, 25, 0, 15, 0, 10 which is pretty much what you figured out by revising column E.... sorry about that... but glad you figured it out.
 
Nevermind, simply changing the values in cells E2, E4 & E6 to 0% resolved the column E issue. Thank you for the solution!

Also you could insert an extra column to cater for percentages above 40 in row one. Even if these aren't valid, where they are part of a table of formula parameters, its better to include them annd return 0, "", "Invalid" or whatever. This is because you have greater flexibility than with the single IFERROR() value, which must cover all errors.
See my attachment.
 

Attachments

  • IndexWithOffset.xlsx
    10.6 KB · Views: 7
Back
Top