Home

VLOOKUP or INDEX MATCH?

%3CLINGO-SUB%20id%3D%22lingo-sub-535345%22%20slang%3D%22en-US%22%3EVLOOKUP%20or%20INDEX%20MATCH%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-535345%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20create%20a%20formula%20that%20will%20A.)%20search%20a%20specific%20cell%20for%20the%20name%20of%20a%20month%20and%20B.)%20apply%20a%20corresponding%20divisor%20to%20cells%20within%20a%20set%20range.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20question%20is%2C%20should%20I%20be%20using%20the%20VLOOKUP%20function%2C%20or%20INDEX%20MATCH%20function%20or%20neither%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAttached%20is%20an%20example%20spreadsheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ED1%20is%20the%20name%20of%20the%20month%20needed%20(current%20month)%3B%20populates%20from%20information%20pasted%20into%20data%20entry%20sheet.%26nbsp%3B%3C%2FP%3E%3CP%3EF2%3AF7%20last%203%20months%20pieces%20sold%3B%20needs%20to%20be%20divided%20by%20the%20total%20number%20of%20working%20days%20over%20the%20past%20three%20months%20(see%20table%202)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETable%201%2C%20number%20of%20working%20days%20per%20month%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETable%202%2C%20sum%20of%20prior%20three%20month's%20working%20days%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20Goal%20of%20this%20formula%20is%20to%20create%20an%20adaptable%20spreadsheet%20that%20will%20update%20according%20to%20data's%20corresponding%20month%2C%20thereby%20calculating%20avg.%20pieces%20sold%2Fday%3C%2FP%3E%3CP%3EAny%20help%20is%20greatly%20appreciated.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESincerely%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJohn%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-535345%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-535543%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%20or%20INDEX%20MATCH%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-535543%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F333039%22%20target%3D%22_blank%22%3E%40J-Griff%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHi%20John%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'd%20recommend%20to%20use%20dates%20(let%20say%20first%20of%20each%20month)%20instead%20of%20texts%20for%20months%20and%20apply%20custom%20format%20as%20mmm%2Fyyyy%20to%20them%20(I%20did%20that%20for%20May%2F2018%20in%20attached).%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhen%20sum%20of%20days%20for%20previous%203%20months%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DSUM(INDEX(%24C%2411%3A%24C%2425%2CMATCH(%24D%241%2C%24B%2411%3A%24B%2425%2C0)-3)%3AINDEX(%24C%2411%3A%24C%2425%2CMATCH(%24D%241%2C%24B%2411%3A%24B%2425%2C0)-1))%3C%2FPRE%3E%0A%3CP%3E%2C%20see%20in%20J2%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-536028%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%20or%20INDEX%20MATCH%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-536028%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F333039%22%20target%3D%22_blank%22%3E%40J-Griff%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20data%20in%20sheets%20Site%201%20and%20Run%20Data%20Sheet%20can%20be%20done%20as%20pivot%20table.%20Provided%20that%20you%20add%20two%20extra%20columns%20to%20the%20sheet%20Data%20Entry.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
J-Griff
Occasional 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.

 

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies