Hello,
I explain my excel problem. In one sheet I have this kind of table (I wrote
2 class, 4 ranges and 3 bonus Type but they could be more):

class million bonus type
ranges A B C
X1 1,5 0,50 0,75 1,00
X1 2,5 0,75 1,00 1,25
X1 3,5 1,00 1,25 1,75
X1 4,5 1,25 1,50 2,25

Y1 1,5 1,00 1,25 1,75
Y1 2,5 1,25 1,50 2,00
Y1 3,5 1,50 2,00 2,50
Y1 4,5 2,25 3,00 3,50

Writing in other sheet the class, range and bonus type (A, B or C) data, I
would like to have bonus value. For example if I write: class = Y1 ; million
range = 1,8 ; bonus type = B the result have to be 1,50 (1,8 million range
is greatest than 1,5 and so the row to extract value is that with 2,5 limit).
I would like to know if it is possible to write an excel formula (no VBA or
filters) to extract values as above explained ?

One way:

1) Modify your ranges to start at 0,0 for the first row, then 1,5 for
the second, etc.

2) Name your table ranges. If the X1 class is in A3:E6, then assign the
name "TableX1" to B3:E6 (note: NOT column A). Similarly, name B8:E11
"TableY1".

3) Name the bonus type range (e.g, making the same assumption as above,
B2:E2) "BonusType"

4) Assume that in your other sheet, you have Class in A1, range in B1,
and Bonus type in C1. You could then use

=VLOOKUP(B1,INDIRECT("Table"&A1),MATCH(C1,BonusTyp e,FALSE),TRUE)





In article ,
Andrea wrote:

Hello,
I explain my excel problem. In one sheet I have this kind of table (I wrote
2 class, 4 ranges and 3 bonus Type but they could be more):

class million bonus type
ranges A B C
X1 1,5 0,50 0,75 1,00
X1 2,5 0,75 1,00 1,25
X1 3,5 1,00 1,25 1,75
X1 4,5 1,25 1,50 2,25

Y1 1,5 1,00 1,25 1,75
Y1 2,5 1,25 1,50 2,00
Y1 3,5 1,50 2,00 2,50
Y1 4,5 2,25 3,00 3,50

Writing in other sheet the class, range and bonus type (A, B or C) data, I
would like to have bonus value. For example if I write: class = Y1 ; million
range = 1,8 ; bonus type = B the result have to be 1,50 (1,8 million range
is greatest than 1,5 and so the row to extract value is that with 2,5 limit).
I would like to know if it is possible to write an excel formula (no VBA or
filters) to extract values as above explained ?

Hi Andrea:

I had to add another column for the min as well as the max millions

1 A B C D E F
2 Class Millions Millions Bonus Bonus Bonus
3 Type Min Max A B C
4 X1 0 1.5 0.5 0.75 1
5 X1 1.5 2.5 0.75 1 1.25
6 X1 2.5 3.5 1 1.25 1.75
7 X1 3.5 4.5 1.25 1.5 2.25
8
9 Y1 0 1.5 1 1.25 1.75
10 Y1 1.5 2.5 1.25 1.5 2
11 Y1 2.5 3.5 1.5 2 2.5
12 Y1 3.5 4.5 2.25 3 3.5
13
14
15 Class X1
16 Millions 2.4 0.75
17 Bonus B

=SUMPRODUCT(($B$4:$B$12=$D$15)*($D$16=$C$4:$C$12) *
($D$16<$D$4:$D$12),INDIRECT("R3C" &
MATCH($D$17,$E$3:$G$3,0)+4 &
":R11C" & MATCH($D$17,$E$3:$G$3,0)+4,FALSE))

Ok it is a litltle tricky but basically you use the sumproduct to find the
row that you want using

(B4:B12=D15)* <<< the class ie X1
(D16=C4:C12)* <<< the millions min check with 2.4
(D16<D4:D12) <<< the millions max check with 2.4

these are all booleans and therefore 1 or 0 and the 1 is the match. Care
needs to be taken with the te limits.... Is 4.5 really the limit I cannot
anser that.

this find the row that we want then we get

then the last part get the column we use indirect based on the match of the
bonus.
I use R1C1 format as it is easier than A1 style when making ranges.

INDIRECT("R3C" &
MATCH($D$17,$E$3:$G$3,0)+4 &
":R11C" & MATCH($D$17,$E$3:$G$3,0)+4,FALSE))

this finds the match as in index number so you add 4 on to it (1 less than
the start) MATCH($D$17,$E$3:$G$3,0)+4
and it gives you the row.

See which one you like.

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"JE McGimpsey" wrote:

One way:

1) Modify your ranges to start at 0,0 for the first row, then 1,5 for
the second, etc.

2) Name your table ranges. If the X1 class is in A3:E6, then assign the
name "TableX1" to B3:E6 (note: NOT column A). Similarly, name B8:E11
"TableY1".

3) Name the bonus type range (e.g, making the same assumption as above,
B2:E2) "BonusType"

4) Assume that in your other sheet, you have Class in A1, range in B1,
and Bonus type in C1. You could then use

=VLOOKUP(B1,INDIRECT("Table"&A1),MATCH(C1,BonusTyp e,FALSE),TRUE)





In article ,
Andrea wrote:

Hello,
I explain my excel problem. In one sheet I have this kind of table (I wrote
2 class, 4 ranges and 3 bonus Type but they could be more):

class million bonus type
ranges A B C
X1 1,5 0,50 0,75 1,00
X1 2,5 0,75 1,00 1,25
X1 3,5 1,00 1,25 1,75
X1 4,5 1,25 1,50 2,25

Y1 1,5 1,00 1,25 1,75
Y1 2,5 1,25 1,50 2,00
Y1 3,5 1,50 2,00 2,50
Y1 4,5 2,25 3,00 3,50

Writing in other sheet the class, range and bonus type (A, B or C) data, I
would like to have bonus value. For example if I write: class = Y1 ; million
range = 1,8 ; bonus type = B the result have to be 1,50 (1,8 million range
is greatest than 1,5 and so the row to extract value is that with 2,5 limit).
I would like to know if it is possible to write an excel formula (no VBA or
filters) to extract values as above explained ?