Looking up multiple criteria and returning a matching column

[Martin Young]

hi

I am designing a spreadsheet to calculate the exgratia payment for
redundancy i am given a grade, Salary and years of service.

I need to look up these values to select the correct ammount from the
ammount column

Eg
Grade ------------> B
Salary ------------> 19500
Length of service ---> 7

I have tried using Vlookup and other functions but i can't work out
which function i should be using. Somewhere in the function the Salary
and length of service (LOS) need to encorporate greater than and less
than.


GRADE SAL_LOW SAL_HIGH LOS_LOW LOS_HIGH AMOUNT
A 0 9999999 0 4 6000
A 0 9999999 5 9 8000
A 0 9999999 10 14 10000
A 0 9999999 15 19 12000
A 0 9999999 20 99 15000
B 0 9999999 0 4 8000
B 0 9999999 5 9 8700
B 0 9999999 10 14 10900
B 0 9999999 15 19 13000
B 0 9999999 20 99 16300
C 0 24999 0 4 7400
C 0 24999 5 9 9800
C 0 24999 10 14 12300
C 0 24999 15 19 14800
C 0 24999 20 99 18500
C 25000 29999 0 4 9000
C 25000 29999 5 9 12000
C 25000 29999 10 14 15000
C 25000 29999 15 19 18000
C 25000 29999 20 99 22600
C 30000 9999999 0 4 10600
C 30000 9999999 5 9 14200
C 30000 9999999 10 14 17800
C 30000 9999999 15 19 21300
C 30000 9999999 20 99 26700
D 0 24999 0 4 7400
D 0 24999 5 9 9800
D 0 24999 10 14 12300
D 0 24999 15 19 14800
D 0 24999 20 99 18500
D 25000 29999 0 4 9000
D 25000 29999 5 9 12000
D 25000 29999 10 14 15000
D 25000 29999 15 19 18000
D 25000 29999 20 99 22600
D 30000 34999 0 4 10600
D 30000 34999 5 9 14200
D 30000 34999 10 14 17800
D 30000 34999 15 19 21300
D 30000 34999 20 99 26700
D 35000 9999999 0 4 11500
D 35000 9999999 5 9 15300
D 35000 9999999 10 14 19100
D 35000 9999999 15 19 23000
D 35000 9999999 20 99 28700
E 25000 29999 0 4 9000
E 25000 29999 5 9 12000
E 25000 29999 10 14 15000
E 25000 29999 15 19 18000
E 25000 29999 20 99 22600
E 30000 34999 0 4 10600
E 30000 34999 5 9 14200
E 30000 34999 10 14 17800
E 30000 34999 15 19 21300
E 30000 34999 20 99 26700
E 35000 9999999 0 4 12300
E 35000 9999999 5 9 16400
E 35000 9999999 10 14 20500
E 35000 9999999 15 19 24600
E 35000 9999999 20 99 30800



Any help is appreciated and thanks in advance
Martin

 

[Andy B]

Martin

There are a few ways to crack this. One way is to use SUMPRODUCT. First,
define names for your ranges, making sure the ranges are all the same size,
and then try this:

=SUMPRODUCT((Grade="B")*(Sal_high<19500)*(Los_low<7)*(Los_high>7)*(Amount))
If you use this formula regularly, instead of the data ("B", 19500 etc) use
cell references.

Andy.

 

[Martin Young]

Thanks Andy,
However the formulai is not returning the correct value i entered:-


=SUMPRODUCT((grade=H2)*(lowsal>=G2)*(highsal<=G2)*(loslow >=
I2)*(loshigh<=I2)*(amount))

where grade is in column A lowsal is in B etc

Can you tell me what i'm doing wrong...

Thanks
Martin