# VLOOKUP or INDEX MATCH?

Highlighted
Occasional Contributor

# 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?

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
Highlighted

# Re: VLOOKUP or INDEX MATCH?

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.

Highlighted

# Re: VLOOKUP or INDEX MATCH?

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.