Need Help with Formula for Eligibility Result Form based on 2 input Variables

**jlepp06** · 06-26-2013, 03:05 PM

Hello -
I'm attempting to create an Eligibility Result Form and need assistance with a formula that will give me what I need.

Background Information:
2 tabs (worksheets)
- Tab 1 (EligibilityForm) acts as the "program". 6 input areas - 2 must be filled in (total #, and one of the other 5) - 1 output area (where formula is needed).
- Tab 2 (Data) is the data tab to reference and verify across to determine eligibility.

What needs to happen is a user will fill out the Total # on the EligibilityForm tab, and only 1 of the other 5 (Annual, Monthy, Twice per Month, Every Two Weeks, and Weekly). The "program" would then take that information, look to the Data tab, it will find the correct row based on the number entered in the Total # field in the EligibilityForm tab. The matching data will be in Column D on the Data tab. Once it finds the correct row, it needs to look in the SCALE A area first (Blue section on the Data tab). It will need to look in the correct column based on which of the 5 fields they filled out on the EligibilityForm tab. If the user filled out the Annual, it needs to look in the Annual column, Monthly, then it needs to look in the Monthly column, etc.

It needs to determine if based on the amount they entered whether it was less than or equal to that amount. If it was not, it then needs to look to the SCALE B area on the Data tab (Green area). Based on what the program determines:

-If less than or equal to in Blue area, it should put SCALE A in the Eligibility Result field on the EligibilityForm.
-If it didn't meet the Blue area's requirements, but met the Green area's requirements, it should put SCALE B in the Eligibility Result field on the EligibilityForm.
-If it didn't meet either of the requirements in the Blue or Green area, it should put NOT ELIGIBILE in the Eligibility Result fiels on the EligibilityForm.

Hopefully this was clear enough of an explanation.
Let me know if you have any questions or if there is a better way about this.
I was trying to use excel as the data on the Data tab changes frequently.

**jlepp06** · 07-16-2013, 01:22 PM

I solved this myself. I went a completely different way about this.

**arlu1201** · 07-17-2013, 05:59 AM

If you solve a problem yourself before anyone else has responded, please take a moment to describe your solution, chances are some other member will benefit.

**jlepp06** · 07-17-2013, 08:08 AM

I don't have my sample file anymore and I don't have time to strip out identifying information at the moment to give the example, but I'll give a brief explanation.

Rather than the 1 eligibility result box, I ended up using a result box for each scenario.
Total of 10 result boxes.

Setup was 2 columns in the result area. One column had 5 result boxes for Scale A, and the other had 5 result boxes for Scale B.

Eligible and not eligible were the values that displayed in the correct box depending on what data was filled in.

Here's a sample formula that was used:
=IF(ISBLANK(F8),"",(IF(ISBLANK(F6),"",(IF(VLOOKUP($F$6,Data!$D$12:$N$31,2)>=F8,"ELIGIBLE","NOT ELIGIBLE")))))

This formula was modifed for each result box.

If/when I get time, I'll create another sample of the completed file for viewing.