Help with getting table value within a range

[RochdaleRules]

Hi

In my screenshot, column m is ElecStart, column n is ElecEnd and column w is ElecTypeRenew (which is text)

I want to be able to abstract the corresponding cell value from w, where current date is beween m and n inclusive.

So, using today’s date of 06/02/2020 (6 Feb 2020) I want the output to be ‘d’.

I have tried using VLOOKUP but cannot get it working. If someone could guide me in the right direction, I would be very grateful.

[anon73440385]

Please upload a sample file, so someone providing an answer could test his answer before posting.

[anon73440385]

Hello,

provided

• Your data start in row 2
• Your date are really LibreOffice dates (i.e. integer numbers)
• Column M is sorted in ascending order

formula: =VLOOKUP(TODAY();M2:W8;11;1) will result in d

Or use (assuming Elec Start is in cell $M$1):

=OFFSET($M$1;MATCH(1;(M2:M8<=TODAY())*(N2:N8>=TODAY());1);10)

Note(s)

• You may replace TODAY() by VALUE("2020-02-06"), thus simplifying adaption to any other date
• Use ISO dates for that (just a recommendation) to support portability.

Hope that helps.

If the answer helped to solve your problem, please click the check mark () next to the answer.

[RochdaleRules]

Thanks for this, it’s perfect. I altered it slightly to cater for futher dates being added and to get the date in question from another sheet. But it does the trick nicely.

=VLOOKUP($Energy.A2,INDIRECT("M2:W"&COUNTA(elecstart)+1),11,1)