Try reading up on the LOOKUP Function. That may be what you need if I understand you correctly.
Close.
I ended-up using the INDEX and MATCH functions together.
First, I set-up a Named Range for the pay grade (and other) data. That way I can make a drop-down list to select the Service Member's (SM) pay grade. This enforces data integrity because end-users will get sloppy and use different "formats" for noting pay grade, i.e. e5, e-5, E-5, etc.
Then I set-up a formula to compute Time in Service (TIS).
=DATEDIF( IF(YEAR(O2) > 1950,O2,TODAY()), TODAY(), "y")The referenced cell is the SM's Pay Entry Base Date (PEBD).
What this translates into is: Find the DATEDIF(ference) IF the year in the referenced cell (O2) is greater than 1950 (any date greater than 40 yrs but less than the year 1900 will suffice), and compare that referenced cell to TODAY and express it in the number of years (y).
On a separate worksheet I placed the Base Pay tables for all pay grades. The pay grades run vertically in the A column with the number of years TIS running horizontally on top from 0 to 40. I did each year of TIS as a separate column to facilitate the above PEBD calculation. You can use a MATCH formula to find the nearest value of a given argument but my form hasn't evolved there as of this writing, though it's actually pretty basic.
I have separate worksheets for BAH (housing), BAS (food allowance), FLight Pay and Hazard Pay. I elected to make each one a separate worksheet so they can be easuily edited once the pay rates change. When the workbook is used I'll hide those tabs to keep the random, wandering lieutenant from mucking things up too badly.
So...
...with pay grade and TIS entered on a Personnel Data Sheet the PerStat form pulls this info and then the rest is getting the PerStat form to grab the monthly pay rate.
In my "Base Pay, Monthly" column I entered:
=INDEX('Base Pay'!$B$2:$AP$25,MATCH(PerStat!B2,'Base Pay'!$A$2:$A$25,0),MATCH(PerStat!C2,'Base Pay'!$B$1:$AP$1,0))Broken down, this means:
Using the INDEX function look on the 'Base Pay' table and select the range of cells from B2 thru AP25.
The dollar signs are an
absolute reference. Without those dollar signs excel will automatically shift the range of cells it looks at everytime your data moves 1 row/record lower. So, as you move down the PerStat form the referenced cells remain static. To accomplish this click on the worksheet then drag-select the range of cells you need and press F4.
Continuing on:
After looking on the Base Pay worksheet perform a MATCH function. Look on the PerStat sheet to see what data you are looking for; in this case it is cell B2. Then go back to the Base Pay sheet and look at the absolute range of cells from A2 to A25 and find an exact match (the 0 argument).
Then do a second MATCH function. Look at the PerStat form data in cell C2 and then look for that data on the Base Pay worksheet and find that value along absolute range B1 thru AP1 and make sure it is an exact match.
Holy crap! It worked!
Then just rinse and repeat for BAH, BAS, etc. and set-up separate columns to calculate the daily rates as well as keeping running totals for each indiviual SM as well as the task Force as a whole. Throw in columns for when State Active Duty (SAD?!?!?!) began and ended with a calculated total number of days to keep a grand total of our burden to the taxpayers.
The officers in G3 seem happy enough.
As this evolves I'm going to work it into an Access database to make the forms, queries and reports easier on the eyes and then get it mounted onto our SharePoint site.
