look up with multiple vertical & horizontal criteria

[apn3a]

hi,

so i am trying to create a formula where i can look up for a value by using multiple criteria. i want also to get the closest approximation value if one of the criteria is not met.

So my Lookup table is this:

E F G H I J K K M

Day Name Intervals Week 15 Week 16 Week 17 Week 18 Week 19 Week 20 Wed John 21:00 14.8 17 18.3 19 21.4 22.1 Wed Maria 11:00 14.8 17 4.8 4 4.6 4.5 Mon Stacey 23:00 14.8 17 3 3.4 3.4 4.2 Wed Howard 14:00 14.8 17 3 3.4 3.4 4.2 Wed Howard 23:00 14.8 17 3 3.4 3.8 4.2 <tbody> </tbody>

<tbody>
</tbody>

and the table i want to return values to (Column E) is this:

A B C D E

Name	Time Slot	Week	Day	Value
John	10:00	Week 19	Wed
Maria	11:00	Week 19	Wed
Alissa	11:00	Week 19	Thu
Howard	13:00	Week 19	Wed
Stacey	13:00	Week 20	Mon

<tbody>
</tbody>

What i want is to return the "Value" from the above table based on the criteria: Name Match, Timeslot Match (closest approximation if not exact value), Week Match and Day Match.

the sumproduct formula works great, but its not suitable for finding approximations.

the index match formula returns me either a wrong value or a #ref error (Ctrl+Shift+Enter) =INDEX($H$2:$M$6,MATCH(C2,$H$1:$M$1,0),MATCH(1,(A2=$F$2:$F$6)*(B2<=$G$2:$G$6)*(D2=$E$2:$E$6),0))

in E1 i expect to see 21.4
in E2 i expect to see 4.6
in E3 i expect to see nothing (0)
in E4 i expect to see 3.4

etc

Thanks!

 

[apn3a]

anyone?