I need help to find a formula that return value related within a given range of time.

Hi, here is my challenge:

In the table A, I need to refer a date to a certain semester.

I have created a matrix that establishes that "Semester 1" (lets say B6) is between two dates (C6 and D6).

This way, once I fill the date cell on the table A, the next cell should search the data range on "semester matrix" and return what is the associated "semester".

I have tried using he formula HLOOKUP but it did not work because it returns the value of a indicated cell, does not consider the range.

Thanks!

Gina

Formulas & Functions

Need Help

SergeiBaklan
MVP
Nov 08, 2017
Hi Gina,

As a variant you may use array formula (Ctrl+Shift+Enter) like
=IFERROR(INDEX(B:B,MATCH(1,(YourDate>C:C)*(YourDate<D:D),0)),"No such")
- Gina Leite
  Copper Contributor
  Nov 08, 2017
  Hi Sergei, thank you for your assistant.
  It has not worked, i think I might be missing a step.
  If you have the time and you don´t mind, can you access this shared draft on excel online to help me out?
  Thank you very much !
  gina
  - SergeiBaklan
    MVP
    Nov 08, 2017
    Gina, I'll do bit later, but, yes, such formula won't work in Excel Online if you use it. Will check the file and try to find alternative.

Forum Discussion

I need help to find a formula that return value related within a given range of time.

Share

Resources