Forum Discussion
VLOOKUP or INDEX MATCH?
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
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.
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.
