1

I have a Pivot Table that I am able to cycle through the different months based on a Month Filter (changing the data of the table). I am trying to calculate based off of that change of value. I am recording the amount of times a license is used in a workday. The number of workdays in a month change based on the month. I calculated out the workdays for each month using 

=NETWORKDAYS(H34,EOMONTH(H34,0),J34:J45)

H34 references my 1/1/2016 date. J34:J45 references my holiday dates to avoid.

I am trying to write a formula that says "If the month listed (from my month filter) within the Months list then to print the corresponding Workdays value.

=IF(G34:G46=H48, I34:I46)

It only works on January. The rest of the values return False. I do calculate it as an Array Function. It returns either False. I cannot think of another function that could replace it. Any help would be appreciated. 

2016            
   Month    Date    Workdays    Holiday Dates   Holidays
  January   1/1/2016    19       1/1/2016   New Year's day
  February  2/1/2016    20       18-Jan     Holiday 2
  March     3/1/2016    22      2/15/2016   Valentines day
  April     4/1/2016    21     3/25/2016    St. Patrick's Day
   May      5/1/2016    21     5/30/2016    Memorial Day
  June      6/1/2016    22      7/4/2016    Independence Day
  July      7/1/2016    21      9/5/2016    Labor Day
  August    8/1/2016    23      10/10/2016  Holiday
September   9/1/2016    22      11/8/2016   Columbus Day
 October    10/1/2016   21      11/11/2016  Veteran's Day
 November   11/1/2016   21      11/24/2016  Thanksgiving
 December   12/1/2016   21      12/26/2016  Christmas
  All              235      

  Selected Month            
    March   FALSE
Improve this question

2 Answers 2

Reset to default
2

The VLOOKUP() function will greatly simplify this process for you. This particular function is great for extracting values in a list based on a key lookup field.

In your example, you want to easily extract the value from Holiday Dates column based on an input month.

enter image description here

A few things to keep in mind for the input parameter functions in this function:

  • The first column in your table array must contain the lookup value, in this case, the month column.
  • If may need to lock your array dimensions, use the $ sign in front of the column letter and row number.
  • FALSE should be selected for the range_lookup in order to do exact matching for the lookup value. If you choose false, you may get false positivies.
  • Every row in your list should be unique for the lookup value. Let's say January was listed twice, the value returned would be for the first occurrence.

Additional info:

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

You can use the LOOKUP function, as follow:

=VLOOKUP(G48,G34:I45,3)

This formula looks up "G48" value in column G (first matrix column), and returns the value from column I (third matrix column) that is in the same row.

Read this official Office page for more details.

Please, give us your feedback.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.