look for a value with multiple variables
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 ?
|