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:
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 17: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 23:00 14.8 17 3 3.4 3.4 4.2
Mon Howard 23:00 14.8 17 3 3.4 3.4 4.2
and the table i want to return values to (Column E) is this:
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
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 the exact value or zero (Ctrl+Shift+Enter) =IFERROR(INDEX($D$2:$I$6,MATCH(1,(A2=$B$2:$B$6)*(B2<=$C$2:$C$6)*(D2=$A$2:$A$6),0),MATCH(C2,$D$1:$I$1,0)),0)
for all the zeros, however, i want the closest approximation in the Timeslot match (+ or -, depending on whats closer)
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!
Bookmarks