Range Lookup with INdex / Match

neilbomb

New Member
Joined
Jul 3, 2010
Messages
1
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.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
If using three criteria will give you a unique result, consider using SUMPRODUCT instead. A typical setup might look like:
=SUMPRODUCT(($A$2:A$200=$H$1)*($B$2:B$200=$J$1)*($C$2:C$200=$K$1)*$D$2:$D$200)

Criteria are in H1, J1, K1; lookup columns are in A, B, C; value to return is in D.
Note --
All lookup ranges must be the same length
You can't use entire columns as ranges

Denis
 
Upvote 0

Forum statistics

Threads
1,225,847
Messages
6,187,366
Members
453,420
Latest member
ESCH1021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top