MS Excel Gurus, a moment of your time (HLOOKUP)

Factual Questions
1

Hey Excel people, I have a little conundrum that I’m trying to sort out and I’m hoping you guys can help.

I’m trying to reverse engineer a old workbook that’s not documented and no one knows how it works. I suspect the problem is a simple one. The confusing part is with a HLOOKUP formula. The worksheet is a long monthly report that calls data from another sheet that contains a wide data array that contains essentially all the relevant data for the business deal. The data array sheet grows every month horizontally by adding a new column for the new month. The data is indexed vertically by row numbers.

The HLOOKUP is written like this:
=HLOOKUP(DC$13,Input,B$14)

The DC13 entry is row one of the data array for the most recent month being reported. B14 entry is the relevant row of data to be returned. A fairly straightforward HLOOKUP. The issue is that stupid Input item. It exists where the data array value would be expected to be. I can hardcode the data array values and make the report work, but it seems that the data array has been somehow assigned a dynamic variable called “Input” to allow the data array to grow without requiring updating every month. Since the report is several hundred rows of lookups that’s a smart strategy and it makes my solution of entering a static data array ineffective.

This technique is something I’ve never tried and I can’t figure out how this spreadsheet is making it work. I can’t find anything that appears to initialize that variable or that assigns it a value.

Anyone have any ideas how to make this work or know where I might find the assignment of that variable? It’s not readily apparent anywhere on the spreadsheet. When I track the formula it pulls up the worksheet with the data array on it but it’s not defined anywhere that I can see and I can’t figure out how to adjust it to include this new row of data.

I’ll try to answer questions as best I can.

2

Input could be named range, but in this case I suspect it is a formula defined as a Worksheet Name. Click Insert/Name/Define and see if it is listed.

3

That appears to be precisely it. Input is a defined array and it needed to be expanded to include the new column of data. Thanks a ton, that’s a feature of Excel I never used before.

4

so what is it? A “Named range” or a “custom formula”. I suspect it is “named range” because it doesn’t have any arguments.

5

I’m not sure, I don’t have the spreadsheet handy at the moment. I suspect that it’s a named range, but the original creator could have defined that range dynamically using a formula. It’ll have to wait til I’m at my home PC. It’s not really important, the “Defined Names” tool in Excel was the foreign one to me, formulas and data ranges I can work with interchangeably.