Recently I had a project that required creating a formula that extracted a workday value from the intersection of a vertical (month) and hortizonal (year) cell reference. In other words, the formula needed to get a value from the following table based on the input of a month and year. The table resides in the range B3:E16 below:

The purpose of the formula is to allow the output to be placed across a horizontal range. In this example, the horizonal range inputs (month and year) are in the range C19:H20. The output range in which the workdays need to be returned resides in C22:H22. After searching the internet for variations of Index and Match, I found that two match functions could work.

The formula in cell C22 is:

=INDEX($B$3:$E$15,MATCH(C20,$B$3:$B$15,0),MATCH(C19,$B$3:$E$3,0),1)

The formula is copied from C22 to H22.

It works even better if you use named ranges. Of course, you wouldn’t be able to autofill it. But it makes the formula much easier to read.

—JP