Formula to select data using Multiple Criteria

**Daphne** · 02-11-2010, 11:45 PM

In the attached vacation accrual spreadsheet,

I created two tables: Exempt hours earned, and also, Non Exempt hours earned.
I have all employees on one worksheet.

The hours accrued (Vacation Level) are different for Exempt and Non Exempt.

I need help on: What is the best formula to autofill the Vacation Level column (H):

It needs to choose exempt or non exempt from column (C) then reference the # Years of Service column (F) to find the correct vacation days earned as defined in the two tables on the right, or those numbers could be used in the formula itself. . . I JUST DON'T KNOW!
Nothing I am trying is working!

Please help or create a formula! Use the spreadsheet to maybe make name ranges? nested IF statements, or LOOKUP, or even VLOOKUP??? Or something that works

not to repeat, but. . . Nothing I am trying is working!

Thank you in advance!

- Daphne

**rylo** · 02-12-2010, 12:24 AM

Hi

Highlight the range O5:P12, and give it the defined name exe. Highlight the range O16:P22, and give it the defined name non.

You could then use the formula along the lines of

=VLOOKUP(F12,INDIRECT(LEFT(C12,3)),2)

HTH

rylo