Index - Match Formula help needed OR Most likely an entirely new formula.... HELP!

Sheet 1, PTAE FY 13 Budget, Cell N3 is where I want the result of the formula that you help me with creating. (Thanks ahead of time).

Sheet 4, BAH Table TN, is where the data resides. Columns A, B, C are named range "Zipcode"; Columns H -X are named range "TNBAH"

I want to automate this cumbersome spreadsheet and thus far my formulas have worked very well; however, this final formula is given me a serious run for my money. The existing formula in cell N3 must be added to this new formula as that answer is to be added to the information that I believe an Index-Match formula will pull into the equation for the final answer.

With that said, I want Cell N3 to take the soldier's Zipcode (Sheet 1, Cell E3) and reference range [ZIPCODE] and determine the AREA; I want it to reference range [SM] to determine if the soldier has Dependents (sheet 2, Pers Oct 2012, Column K); and finally reference range [SM] and determine the soldiers Rank (Sheet 2, Pers Oct 2012, Column E).

Some Caveats.

1. Sheet 2, Pers Oct 2012, Columns E and F mean the same, but Column E is needed for the Paydata information and then on the form (Sheet 1) the persons rank (Column F) is concatenated with the soldiers name.

2. For Range [ZIPCODE], if the formula does not find the keyed zipcode (such as that in the example) then I want the formula to use the Table on Sheet 4, BAH Table TN, Columns W-X (ZZ590); otherwise, match the Area listed in Column B with the tables in columns I-V.

3. Sheet 2, Range [SM], the number of dependents will range from 0-?? for each soldier.

From the sample data, the formula would pull:

Zipcode: 38451
Dependents: (If >=1, "yes", "no") YES
Rank: E-7

With those data points, use the range [Zipcode] to determine if the zipcode keyed in Cell E3,Sheet 1, is not present in [Zipcode] and know to use table ZZ590 on columns W-X. It will see the soldier has dependents and use column W (ZZ590 table); It will then find the corresponding dollar amount for that soldier Rank (E-7 in this example) with the answer being: $1,245.00

With that answer in hand, that $1,245.00 will be added to the formula currently in cell N3 showing a grand total of $1,593.44

(Sigh!) What are your questions and I look forward to seeing your replies. I have tried many times using Vlookup and Index-Match combinations to no avail.

Good morning,

Thanks for looking at this complicated problem with me and I am looking to learn from this process so treat me like a child when explaining the more complex formula issues.

that is correct for the zipcode in the example, which a fact for some smaller cities and why those unlisted zipcodes need to pull from the ZZ590 table.

I received this spreadsheet from another department and I am still working to correct the cell formats so thanks for catching that error.

Originally Posted by FDibbins

MATCH(I3,'BAH TABLE TN'!$H$4:$H$30,0)+3

the match() range starts H4, but the index range (your named range TNBAH) starts in H1. the +1 adds the 3 extra rows needed

That makes sense, although I did not know that you could do that. Cool.
I really appreciate your help and I look forward to discussing this to fruition.

have a great weekend.

Good morning,

I want to thank you again for assisting me with this problem as the formula is appears to be working great. How do I acknowledge that you answered this correctly and give you the credit?

One more request. The 1199 Column is the sum of the individuals BAH and BAS. Beginning in JAN-13 (column X), both BAH and BAS are increased by the percentage keyed in B3 and C3. Here is what I have partially working:

=IF(ISBLANK(D29),"",O29+(O29*$B$3))+(IF(LEFT(J29)="E",PayData!$D$30,IF(OR(LEFT(J29)={"O","W"}),PayData!$D$29,""))+(IF(LEFT(J29)="E",PayData!$D$30,IF(OR(LEFT(J29)={"O","W"}),PayData!$D$29,""))*$C$3))

When the soldier's SSN is keyed in column D, it appears that the amount shown column X is correct, but when the SSN is blank the sheet shows the error "#value!" in the cell.

First, would you mind confirming that my formula IS providing the correct figure.
Second, what must I do to remove the error message "#value!" when the SSN is not filled in?
Lastly, what is a better formula to perform this calculation?

Again, thank you!

Tony

FDibbins,

To ensure your formula was fully working I changed the number of dependents (column K on the PERS sheet) to see if the appropriate BAH table was used and it does not appear to be working correctly.

Looking at sheet BAH TABLE TN you will note that each area had two payment columns: With Dep and Without Dep (Dependents). To explain, an E5, living in Chattanooga, with 1 or more dependents (identified in Column K on sheet PERS Oct 2012), will receive $1,125. By comparison, an E5, living in Chattanooga, with zero dependents will receive $921.

What is your suggestion for making this possible with all of the areas?

Thank you,

Tony

good morning,

I updated the spreadsheet with your above formula and it is working as requested. THANK YOU!

One more (hopefully the last for this post) question: How do I adjust the columns across the spreadsheet to include a payraise based on TIS? A soldier's TIS (Column M sheet Pers OCT 2012) is calculated based on Column L. When the soldier is moved into the next pay bracket, i.e. an E-7 with 25 years is paid $4,496.40 (located at Cell P17 sheet PayData) while an E-7 with 26 years is paid $4,815.90 (located at Cell Q17 sheet paydata), in the month of APRIL - how do I made this adjustment?

The amount entered into P3 will be correct for the soldier's pay at that time (OCT of that year), but when his anniversary date arrives in the following year, say in July, then how do I make that month look at the soldier's PEBD (Column N) and determine if he moves into the next pay bracket. If he does, then apply the new pay amount to that cell and adjust the pay to the right.

I am playing with the formulas to figure this out but I am struggling...

Thanks

Tony

Once again, you are dead on target! Thank you... Where do I send the check anyway?

Believe me, I am beside myself with excitement. With this formula I did not know that I could use the dates along the top for each month as part of calculation. You have given me plenty to study and learn from and for that I am very thankful!

How do you work through the problem to come with those solutions? When I define the problem out loud it makes sense, but when I try the formulas it gets all screwed up....

Thank you,

Tony