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
<tbody>
</tbody>
and the table i want to return values to (Column E) is this:
A B C D E
<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!
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
<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!
Last edited: