Hi, im trying to combine Lookup with Index and match.
My problem is that i would like to Lookup up a certain value in a range to return a value representing the range but with 3 criteria to Index or Match - Not sure if its making any sense.
Here's how it looks like.
LVLNameTenureSales %Sales % ScoreQualityQuality ScoreLVL0John Doe11-30 Days73.54% 85.00% LVL1John Doe2BSC33.00% 90.00% LVL2John Doe3BSC65.00% 69.00%
LevelTenureMetricScoreRangesRemarksLVL01-30 DaysSales %1.0000.6899FailedLVL01-30 DaysSales %1.500.690.7099FailedLVL01-30 DaysSales %2.000.710.7299PassedLVL01-30 DaysSales %2.500.730.7499PassedLVL01-30 DaysSales %3.000.751PassedLVL031-60 DaysSales %1.0000.6899FailedLVL031-60 DaysSales %1.500.690.7099FailedLVL031-60 DaysSales %2.000.710.7299PassedLVL031-60 DaysSales %2.500.730.7499PassedLVL031-60 DaysSales %3.000.751PassedLVL061-RegSales %1.0000.6999FailedLVL061-RegSales %1.500.70.7199FailedLVL061-RegSales %2.000.720.7399PassedLVL061-RegSales %2.500.740.7599PassedLVL061-RegSales %3.000.761PassedLVL1BSCSales %1.0000.7199Failed
Condition:
- On the Blue Cell, it needs to Lookup the value (73.54%) in the ranges shown above, considering LVL, Tenure as another Criteria.
Really appreciate your help on this.
Thanks.
My problem is that i would like to Lookup up a certain value in a range to return a value representing the range but with 3 criteria to Index or Match - Not sure if its making any sense.
Here's how it looks like.
LVLNameTenureSales %Sales % ScoreQualityQuality ScoreLVL0John Doe11-30 Days73.54% 85.00% LVL1John Doe2BSC33.00% 90.00% LVL2John Doe3BSC65.00% 69.00%
LevelTenureMetricScoreRangesRemarksLVL01-30 DaysSales %1.0000.6899FailedLVL01-30 DaysSales %1.500.690.7099FailedLVL01-30 DaysSales %2.000.710.7299PassedLVL01-30 DaysSales %2.500.730.7499PassedLVL01-30 DaysSales %3.000.751PassedLVL031-60 DaysSales %1.0000.6899FailedLVL031-60 DaysSales %1.500.690.7099FailedLVL031-60 DaysSales %2.000.710.7299PassedLVL031-60 DaysSales %2.500.730.7499PassedLVL031-60 DaysSales %3.000.751PassedLVL061-RegSales %1.0000.6999FailedLVL061-RegSales %1.500.70.7199FailedLVL061-RegSales %2.000.720.7399PassedLVL061-RegSales %2.500.740.7599PassedLVL061-RegSales %3.000.761PassedLVL1BSCSales %1.0000.7199Failed
Condition:
- On the Blue Cell, it needs to Lookup the value (73.54%) in the ranges shown above, considering LVL, Tenure as another Criteria.
Really appreciate your help on this.
Thanks.