Lookup Problem...Index Match?! Help!

Hi All,

So i have a problem i want to run past you all...

I have a hidden sheet which has Numbers (1,2,3,4,5 etc. moving down Column A i.e. A1 = 1, A2 = 2, A3 = 3 etc.) this is the WEEK number that I am looking to return.

Then, next to each number, there is a list of 7 dates, which fall into that week i.e. A1=1, B1=01/01/2018, C1=02/01/2018, D1=03/01/2018...up to H1=07/01/2018).

What I am trying to do is (on another sheet) have a formula which will look in the range B1:H9999 for a date, and once found, return the WEEK number (Column A) that this date falls within?

Hope that makes sense?

Let me know if you need an example...

Hope you can help!!!


Chris

Try using the WEEKNUM formula.

Hi Kersplash,

Apologies...I should mention...i thought about that; but this is for a second job I have to log my hours and when they'll be paid? Their weeks run from Friday of Week 1, to Thursday of week 2 (Inclusive). Next week begins from the next friday to the following Thursday; and so on...

WeekNum wouldnt work I assume then? or would it?

If you look at the syntax that shouldn't be a problem.

https://exceljet.net/excel-functions/excel-weeknum-function