Vlookup dependant on current quarter

[Godders199]

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.

 

[Scott T]

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]

sorry, could you help with the code for the test which quarter, i dont know where to start.

 

[Roger Govier]

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

[COLOR=#333333].Formula = "=VLOOKUP(a2,'check summary'!$A$1:$s$" & x & ",20 - QOdd,0)"
[/COLOR]

 

[Godders199]

thanks, will give it a go

 

[Navi_G]

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.

 

[Godders199]

you need to post this as a new thread

 

[Navi_G]

i post but no reply from any one....


https://www.mrexcel.com/forum/excel...typical-values-vlookup-including-max-min.html