A lookup function to search through array and return row number

Hi. I am working with essentially the data below:

A B C D E

21 - #1 2008 2012 2016 2020
22 - #2 2009 2013 2017 2021
23 - #3 2010 2014 2018 2022
24 - #4 2011 2015 2019 2023


I am trying to get a formula that will search for the year in A5 in the above array and return a result of the # in column A corresponding to the row of the year.

HLOOKUP(A5,A21:E24,1,FALSE) is what I've been trying, but I get an N/A response. Am I confusing the H and V LOOKUP functions? Or do have the wrong function altogether?

Thanks!!

NBVC - I'm not sure what's happening, but I always get #1.

Cutter - I am trying to see which row the year in A5 is in and then return the column A description for that row (#1, #2, #3, or #4). I hope that makes sense. So I'm not sure which of your suggestions fits that, if any.

Thanks guys for your help. I was apparently wrong in thinking an easy lookup formula would work.

NBVC - Thanks that works now. But when I copy and paste to the next worksheet, I get a VALUE error. Everything is the exact same as far as I can tell, including formatting. After doing the control+C for the cell, I copied the formula directly in the formulas bar and tried copying that. Same error. Any ideas? Thanks!

I think the matches are to be exact. The dates will be either 2010, 2011, 2012 etc etc. My actual array extends further than the example - through 2051. I should be covered for a while. I need to aske if the year in A5 matches a year in one of the rows (which it will) and then give me the corresponding value in the first column of that same row. To me that's an exact match, but maybe we're talking different languages here. Excel has my head spinning lately.

The date in A5 is actually in a this format dddd,mmm,dd,yyyy and I'm using YEAR($A$5) in the formula. All the years in the array are formatted as numbers. I typed in the first year in each row and then used a B21+4 formula etc to get the next numbers. Is that screwing something up?

Thanks for trying to help me work this through!

I take it the formulas I gave do not work???

Cutter - your suggestions weren't working either. The first sheet was fine, but then I got some weird answers.

NBVC, attached is a pared down sheet.

So, in O4 and P4, I am looking to test the year of the date in A5 to see which of the years of the election cycle it is in based on the tab Cycles and the rows 20-23. So 2010 should be found in Row 22 and return the word midterm in O4. AS you can see, it works in the 2010 tab, but when I copy it to 2011 it doesn't work. But then it works again in 2012. In the full sheet I have 3 tabs that don't work and 7 that do. If you can see what the difference is, I'd love to know.

Thanks again!!

NBVC - closing and re-opening the spreadsheet fixed the problem.

Cutter - thanks, that works, too.

I guess NBVC is right - where there is a will, there are many ways.

Thanks guys!!!!