# Is Excel VLOOKUP the best choice for my application?

I have an Excel file that tracks the maximum elevation of the sun throughout the year, one row per day, given a specific lat/lon. If I edit the lat/lon the daily maximum elevation changes.

I'd like to write a formula that queries the entire database (2 columns, 366 rows) and give me the day when the sun first begins reaching a given elevation, and then again when it no longer reaches that elevation.

For example, Honolulu will experience a max solar elevation angle of 80 degrees on May 1, remain at 80 degrees or higher (once daily) until Aug 19th when it drops below 80 degrees again.

I'd like an Excel function that generates these two dates, given that I already have the elevation data.

Thanks!

New contributor
Sebastian is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
• Please can you include mock-up sample data with expected output? – JvdV Aug 13 at 19:21

The solution here is to use a combination of `INDEX()` and `MATCH()` in an array formula.

I wrote two array formulas that solve the problem.

Start Date: `{=INDEX(Dates, MATCH(TRUE, (Elevation>=Find_Elevation), 0), 1)}`

End Date: `{=INDEX(Dates, MATCH(TRUE, (Elevation>=Find_Elevation), 1), 1)}`

Here is how they work

• `Elevation` is a named range that holds every elevation for the year, `Find_Elevation` is a named range that is the the one we're looking for. In your example, this is `80`
• The expression `(Elevation>=Find_Elevation)` returns an array of `TRUE` and `FALSE` (which is why this needs to be an array formula). It returns `TRUE` whenever the elevation for each day is greater than or equal to the elevation you're looking for.
• The `MATCH()` function with third argument `0` returns the index of the first time it sees `TRUE`. This is the index of the first day that the sun is above the elevation you're looking for.
• Using third argument `1` in the `MATCH()` function has it return the last time it sees `TRUE`. This is the index of the last day the sun is above the elevation you're looking for.
• We pass both of these as the second argument to `INDEX()`, and it returns the date corresponding to the value returned by `MATCH()`

I used FormulaChop to generate the formulas above (Full Disclosure: I wrote FormulaChop). Here is a screenshot of the how FormulaChop displays this formula. Here is a link to a spreadsheet that demonstrates the formula. Please excuse the fact that I had to make up the solar elevations.

New contributor
FormulaChop is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.