VLOOKUP or INDEX MATCH?

Copper Contributor

Hello,

 

I am trying to create a formula that will A.) search a specific cell for the name of a month and B.) apply a corresponding divisor to cells within a set range. 

 

My question is, should I be using the VLOOKUP function, or INDEX MATCH function or neither? 

 

Attached is an example spreadsheet.

 

D1 is the name of the month needed (current month); populates from information pasted into data entry sheet. 

F2:F7 last 3 months pieces sold; needs to be divided by the total number of working days over the past three months (see table 2)

 

Table 1, number of working days per month

 

Table 2, sum of prior three month's working days

 

The Goal of this formula is to create an adaptable spreadsheet that will update according to data's corresponding month, thereby calculating avg. pieces sold/day

Any help is greatly appreciated. 

 

Sincerely,

 

John

2 Replies

@J-Griff 

 

Hi John,

 

I'd recommend to use dates (let say first of each month) instead of texts for months and apply custom format as mmm/yyyy to them (I did that for May/2018 in attached).

 

When sum of days for previous 3 months could be

=SUM(INDEX($C$11:$C$25,MATCH($D$1,$B$11:$B$25,0)-3):INDEX($C$11:$C$25,MATCH($D$1,$B$11:$B$25,0)-1))

, see in J2 attached.

@J-Griff 

The data in sheets Site 1 and Run Data Sheet can be done as pivot table. Provided that you add two extra columns to the sheet Data Entry.