Vlookup dependant on current quarter

Godders199

Active Member
Joined
Mar 2, 2017
Messages
293
Office Version
2013
Hello, i currently have the following vlookup code which works find, however i need to hopefully change it dependant on the Quarter of the calendar year we are in.

With ActiveSheet
LR = .Cells(.Rows.Count, 25).End(xlUp).Row - 1
With .Cells(2, 12).Resize(LR)
.Formula = "=VLOOKUP(a2,'check summary'!$A$1:$s$" & x & ",19,0)"
.Value = .Value
.Replace "#N/A", ""
End With
End With

for example in in Quarters 1 and 3 this vlookup is correct, however in quarters 2 and 4 it would need to look at column 20 not 19.

Is it possible to code this in? or do i need to come up with another solution.
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,611
Office Version
365, 2016
Platform
Windows
Use a if statement to test which quarter you are in and if it is 1 or 3 then your formula above else the vlookup with column 20.
 

Godders199

Active Member
Joined
Mar 2, 2017
Messages
293
Office Version
2013
sorry, could you help with the code for the test which quarter, i dont know where to start.
 

Roger Govier

Active Member
Joined
Jun 19, 2002
Messages
303
Hi

You haven't said what your variable is for Quarter number, but assuming it to be Qno
Qno Mod 2 will return 1 if it is and odd Quarter number, or 0 if it is Even.
So, you could avoid the IF test by calculating a new variable , say QOdd
Qodd= Qno Mod 2
then just make your formula
Code:
[COLOR=#333333].Formula = "=VLOOKUP(a2,'check summary'!$A$1:$s$" & x & ",20 - QOdd,0)"
[/COLOR]
 

Godders199

Active Member
Joined
Mar 2, 2017
Messages
293
Office Version
2013
thanks, will give it a go
 

Navi_G

New Member
Joined
May 30, 2018
Messages
24
Dear Experts i found in typical vlookup situation. i use vlook up function but i can't use with other functions
please advice me on such lookup sitations

Find ???? Maximum Marks in each Subject of 3 Student
Find ???? Minmum Marks in each Subject of 3 Student
Find ???? Top 3 Students
Find ???? Down 3 Students
Find ???? Less then 33 Marks Students
Find ???? More then 80 Marks Students
Find ???? And a chart to Explain these values

My boss want to low production of employees and higher production.
i don't know how is it possible...


Question are above mention please advice me. How allaborate them.:confused:
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,098,863
Messages
5,465,129
Members
406,414
Latest member
Discorz

This Week's Hot Topics

Top