# Vlookup dependant on current quarter

#### Godders199

##### Active Member
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.

### 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
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
sorry, could you help with the code for the test which quarter, i dont know where to start.

#### Roger Govier

##### Active Member
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
Code:
``````[COLOR=#333333].Formula = "=VLOOKUP(a2,'check summary'!\$A\$1:\$s\$" & x & ",20 - QOdd,0)"
[/COLOR]``````

#### Godders199

##### Active Member
thanks, will give it a go

#### Navi_G

##### New Member
Dear Experts i found in typical vlookup situation. i use vlook up function but i can't use with other functions

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. Last edited:

#### Godders199

##### Active Member
you need to post this as a new thread