Relative Row Number in HLOOKUP

[sunsoar77]

Hi all,

I'm looking for some assistance with a relative reference in an HLOOKUP function.

The workbook tracks a number of statistics each week by area using an individual worksheet for each area. I have a summary worksheet that will pull through data for each area for the date in question. This way I can keep weekly statistics and review information for trends etc, whilst still showing a snapshot of current week to area management.

I was wondering if there was a way to get a relative row number to populate in the HLOOKUP function?

So instead of having to manually change the row number in each formula on set up (and have Excel throw up heaps of the 'this formula differs' warnings) is it possible to have the row number populate relative to it's row number in the array I'm looking up?

using this:

=HLOOKUP($E$1,Area1!$A$5:$D$11,2,0)

Test work book attached.

HLOOKUP TestBook.xlsx

I've had a look at the ROW and ROWS functions but can't see how they will help, so I'm turning to the greater community for some assistance.

Thanks in advance.

[Pete_UK]

I've made up some data to fill the tables in the three Area sheets, and in the Summary sheet I have put this formula in cell B6:

Please Login or Register  to view this content.

which can then be copied across and down.

Then you can just change the date in E1 to see the numbers change.

Is this what you were after?

Hope this helps.

Pete

[sunsoar77]

Works a treat Pete. Thank you very much.

I'm comfortable with INDEX/MATCH however have only used INDIRECT once before.

I even tried using the INDEX(Ref,MATCH,MATCH) for this instance but couldn't wrap my brain around it, and I think I need to get a better understanding of INDIRECT to make my formula's a little more flexible.

Thread marked solved.