Expert: Gyula Gulyas - 9/5/2007

Question
good day sir! i thank you deeply for the help. Can i ask you again? Here it goes:

I have 2 sheets - sheet1(induct) sheet2(gs&hs)
In sheet 1 are the following
A           B
Excess   Allowance
Years
1    1,500.00
2    1,425.00
3    1,325.00
4    1,250.00
5    1,200.00
6        800.00

In sheet2 here it goes
 A    B        C  
Name  Years  Allowance ---> here i added a command button

Jo     2.6    
Dy     .49
Lim    5.1
Tiu    1.2
Rey    3.3

What i want to have is when i push the command button the macro will place the allowances in column C in sheet2.

Basis will be from
  1 to 1.99 = 1500
  2 to 2.99 = 1,425.00
  3 to 3.99 = 1,325.00
  4 to 4.99 = 1,250.00
  5 to 5.99 = 1,200.00
  6 to 6.99 =   800.00

From sheet two you see that there are different values in column B or the no. of years.

I hope you could help me sir.

Thank you very much

Answer
You can use the VLOOKUP function in Excel to do this with NO VBA macro needed.

the lookup table should be similar to:
0   0   --- > or whatever applies between 0-0.99
1   1500
2   1425
3   1325
4   1250
5   1200
6   800
7   0  ----> or whatever applies 7 and above

Use the VLOOKUP with FALSE as the last argument so it returns exactly what you want. More in Help about the VLOOKUP function.

HTH,
Gyula