Link to home
Start Free TrialLog in
Avatar of -Polak
-PolakFlag for United States of America

asked on

Use Lookups to Simplify IF Long Formula

Hi Experts, I was hoping you could assist in helping me use indirects/lookups to simplify a an IF formula that I have.
Basically I want the indirect to look up the relevant value in the correct "XXXHrPerSortie" worksheets based on the selection in D14 .

The reason I'm looking for this is I'm wanting to expand D14 to have "Custom 1", "Custom 2", "Custom 3", etc.... and I do not want to have to limit/write the current formula up to a certain Custom Number.

Current Formula:
=IF($D$14="SOUTH WEST REGION (SWR)",$D$57*(INDEX(SWRHrPerSortie!I2:I8,MATCH($I3,SWRHrPerSortie!H2:H8,0)))/LawHours, IF($D$14="SOUTH EAST REGION (SER)",$D$57*(INDEX(SERHrPerSortie!I2:I8,MATCH($I3,SERHrPerSortie!H2:H8,0)))/LawHours, IF($D$14="NASO - NATIONAL AIR SECURITY OPERATIONS (NSO)",$D$57*(INDEX(NASOHrPerSortie!I2:I8,MATCH($I3,NASOHrPerSortie!H2:H8,0)))/LawHours, IF($D$14="NORTHERN BORDER REGION (NBR)",$D$57*(INDEX(NBRHrPerSortie!I2:I8,MATCH($I3,NBRHrPerSortie!H2:H8,0)))/LawHours, IF($D$14="HQ AND CENTERS",$D$57*(INDEX(HQCHrPerSortie!I2:I8,MATCH($I3,HQCHrPerSortie!H2:H8,0)))/LawHours, IF($D$14="All",$D$57*(INDEX(AllHrPerSortie!I2:I8,MATCH($I3,AllHrPerSortie!H2:H8,0)))/LawHours))))))
ASKER CERTIFIED SOLUTION
Avatar of Glenn Ray
Glenn Ray
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of -Polak
-Polak
Flag of United States of America image

ASKER

Okay, I followed you up until 
&"!I2:I8")

Open in new window

LawHours exists in Constants!$L$3 not in I2. I2 on my spreadsheet is just the title of the I3:I8 range. Further, LawHours is constant and does not change from the SWR, SER, NASO, NBR, HQC, ALL so I am confused why it is part of the vlookup....
Avatar of Glenn Ray
Glenn Ray
Flag of United States of America image
Each of your original sub-formulas were dividing the $D$57*INDEX(...(MATCH(...)) result by LawHours, so I'm continuing that here.  It doesn't matter where LawHours exists, just that it does.  If you want to move my placement to another sample sheet, it will still work.

Note that I had to create this from scratch just guessing on the values shown in your initial formula.  A redacted example workbook will always help Experts provide a more-meaningful result.
Avatar of -Polak
-Polak
Flag of United States of America image

ASKER

Figured it out! I had moved around a column that was causing an #N/A value and I thought it had to do with LawHours; your solution worked great thank you!
Avatar of Glenn Ray
Glenn Ray
Flag of United States of America image
You're welcome; glad I was able to help.
-Glenn
Avatar of -Polak
-Polak
Flag of United States of America image

ASKER

As a follow-up thanks for letting me know that indirects can be used to lookup the values on Named Ranges. I did added  this yesterday to the formula and it vastly simplified my life.

=IFERROR(IF($D$23<>"Manual Asset",($D$57*(INDEX(INDIRECT(VLOOKUP($D$29,WorkforceReqsLookup,2,FALSE)&"!H2:H8"),MATCH($I3,INDIRECT(VLOOKUP($D$29,WorkforceReqsLookup,2,FALSE)&"!G2:G8"),0)))/LawHours), ((($D$61*(INDIRECT(VLOOKUP($D$29,WorkforceReqsLookup,3,FALSE))))+(INDEX(INDIRECT(VLOOKUP($D$29,WorkforceReqsLookup,2,FALSE)&"!H2:H8"),MATCH($I3,INDIRECT(VLOOKUP($D$29,WorkforceReqsLookup,2,FALSE)&"!G2:G8"),0))-(INDIRECT(VLOOKUP($D$29,WorkforceReqsLookup,3,FALSE)))))*$D$63/LawHours)),0)