Date lookup in 2 different ranges depending on year

[DPoli]

I am looking at data in positive and negative entries in my school. They have asked me to compare across years. I have assigned each date a school week and term but this is difficult across years as they change.
Each entry has a date in B2, I have then pulled the year in V2. I have a seperate sheet 'Reference Data' which has columns A, B, C, D having start date, end date, Week and Term e.g. 29/01/2024, 4/02/2024, T1W01 (Stands for Term 1 Week 1), 1 (Stands for Term 1. This is done for the year.
I use =VLOOKUP(B2,'Reference Data'!$A$2:$D$100,4) to return the date and the same for the week in column 3.

The problem I have now is I put in F G H I the same data for school weeks and terms but for 2023. How can I look in one range if V2 says 2023 and another if it says 2024 (posssibly another for 2025/2022).

Thanks

 

[etaf]

There is most likely a better way then vlookup - as using 365 version of excel - like filter .. BUT not sure on layout exactly

if it works for the 2024 - can you not just add to columns A,B,C,D in the reference data the years 2022, 2023, 2025 etc

you could use an indirect() to get the range for the year, if you wanted different ranges to be looked up
=VLOOKUP(B2,INDIRECT(D2),4,TRUE)
where i have added an IF into D2 to provide the range
=IF(YEAR(B2)=2024,"'reference data'!$A$2:$D$100",IF(YEAR(B2)=2023,"'reference data'!$F$2:$I$100"))
so that IF could be nested for all years and ranges

NOTE- FYI
indirect() are volatile and so can slow a spreadsheet down as they have to be calculated everytime the spreadhseet is calculating, depends on how much is going on in the spreadsheet on the effect it may have

Book2
	A	B	C	D	E
1				range
2		1/1/23		'reference data'!$F$2:$I$100
3
4		i2
Sheet1

Cell Formulas
Range	Formula
D2	D2	=IF(YEAR(B2)=2024,"'reference data'!$A$2:$D$100",IF(YEAR(B2)=2023,"'reference data'!$F$2:$I$100"))
B4	B4	=VLOOKUP(B2,INDIRECT(D2),4,TRUE)

reference sheet

lookup ranges based on year - ETAF.xlsx
	A	B	C	D	E	F	G	H	I
1	2024
2	1/1/24	B2	C2	D2		1/1/23	g2	h2	i2
reference data

On a share for a limited time

Dropbox

www.dropbox.com

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[DPoli]

Thank you so much I tried a few ways that weren't working but have now figured out why. I will definitely be back with more questions!

 

[etaf]

you are welcome