Retrieve Value from table using several criteria.

[mark_luke]

I'm looking for a formula I can put in cell M7 of the attached spreadsheet and using the criteria in cells M1:M6 to pull a rate from the table in the spreadsheet. In column N, I have indicated how the criteria needs to match the table. Using my example criteria in cells M1:M6, the formula needs to return a rate of 14.55%. Any help would be appreciated.

[tim201110]

=INDEX($F$1:$J$22,AGGREGATE(15,6,ROW($A$3:$A$22)/(ABS($E$3:$E$22-M1)=MIN(IF((ABS($B$3:$B$22-M5)=MIN(IF(($D$3:$D$22=M3)*($C$3:$C$22=M4)*($A$3:$A$22=M6),ABS($B$3:$B$22-M5))))*($D$3:$D$22=M3)*($C$3:$C$22=M4)*($A$3:$A$22=M6),ABS($E$3:$E$22-M1)))),1),M2) CSE
why not 10/05%?

[mark_luke]

Thanks Tim. I don't want the range that is equal to 15000. I need the closet range that is greater than 15000, so I'm expecting 14.55% instead of 10.05%. I also need to have the formula dynamic enough to match the year in cells F2:J2, if those years were in a random order instead of sequentially starting with 1.

[wyowhite]

mark_luke, What would you expect with the following criteria?
Lookup Date 7/12/2019
Lookup Year 3
Lookup Period 3
Lookup Region S
Lookup Range 20000
There are 3 matches to that set.

[mark_luke]

If multiple matches, pull the first match (row 16).

[wyowhite]

The fix will test if the result is blank(0). if so use the row (added in M12) and index row+1The row number for the index is array of all of the criteria.


It is all PURPLE SMOKE & MIRRORS

[mark_luke]

These formulas are not meeting my needs for the criteria I have stipulated. The closest formula I can get is one that I have come up with in this spreadsheet. On Sheet 1, I have a formula in cell M7 that pulls the correct value from the table based on the inputs I have. The formula needs to match the Type, Region, Period & Year exactly along with finding the closest date that is less than or equal to the input criteria and then find the closet range that is greater than the input criteria (excluding rates from table that are blank). I need to fix the formula so it doesn't recalculate the rate in the table. For example on Sheet 2, the formula has recalculated the rate and is pulling 2.288% instead of 3.00%. The formula can use something other than aggregate, as long as it pulls the correct rate.

[FlameRetired]

Please try array entering

Please Login or Register  to view this content.

BTW:
Are you open to helper column(s)? I believe this could be far simpler.

[mark_luke]

Can you please add the date check to the formula? I would be open to helper columns.

[FlameRetired]

Can you please add the date check to the formula? ...

That's what this part does.
MATCH($M$2,$F$2:$J$2,0)

[mark_luke]

That part is matching the year to get the correct column, but the formula does not reference column E or the date in cell M1, which is also needed to get the correct row.

[FlameRetired]

In the attached please find in helper column K

Please Login or Register  to view this content.

Then in M7

Please Login or Register  to view this content.

[wyowhite]

You changed the criteria. You now state that the range is determined after the the date is determined. Not in the original request.

[mark_luke]

You changed the criteria. You now state that the range is determined after the the date is determined. Not in the original request.

Because of forum rules, I was not allowed to have another thread. Your formula doesn't work for either request.

[mark_luke]

I've solved the formula that is needed.