Excel-look up factor based on two variables

[ps40life]

Hello,

I have a factor table that looks something like this:

Dep. 20% 30% 40% 50%
1 26500 35200 41200 45500
2 30000 36000 45000 51000
3 32000 37500 41250 47000

The numbers under the percentages are earnings, so with one dep up to 26500, you get a 20% discount. Two dep between 30001 and 35599, you get a 30% discount. How can I pull back the discount % into my formula??

Thanks for any suggestions.
ps40 life : )

 

[SkipVought]

Hi,

NORMALIZE your data and then use a simple lookup

NORMALIZE Your Table using the PivotTable Wizard faq68-5287

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]

 

[ps40life]

OK, great. I normalized the data and it looks good. Great tip! Now, when you say simple lookup, a vlookup only can use one factor. I have heard you can use Index and Match to do a multiple variable lookup, but that is usually when the factors include one column variable and one row variable-thereby, indicating an intersecting cell. So, how do I take two factors, both listed in columns.

 

[SkipVought]

using named ranges...
[tt]
=INDEX(OFFSET($H$3,MATCH(A1,Dept,0),1,COUNTIF(Dept,A1),1),IF(ISNA(MATCH(B1,OFFSET($H$3,MATCH(A1,Dept,0),2,COUNTIF(Dept,A1),1),1)),1,MATCH(B1,OFFSET($H$3,MATCH(A1,Dept,0),2,COUNTIF(Dept,A1),1),1)+1),1)
[/tt]
for the table as
[tt]
Dept Pct Value
1 0.2 26500
1 0.3 35200
1 0.4 41200
1 0.5 45500
2 0.2 30000
2 0.3 36000
2 0.4 45000
2 0.5 51000
3 0.2 32000
3 0.3 37500
3 0.4 41250
3 0.5 47000
[/tt]
values over the max are not expected.


Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]