Within Excel, I need a way to calculate the row reference part of a Cell reference within the VLOOKUP Function... What I am trying to do is return the second column, every 7th row within a range of cells. For example, in a list of daily weight measurements, I am trying to extract into another list the weight for every monday....

I am using VLOOKUP which is returning the correct data, but 7 times until it gets to the next Monday and returns that Monday's data 7 times, and so on. By reducing the size of the range of cells queried (by virtually removing the previous 7 rows each time) I can get the following formula to work, but I have to manually edit the row reference (eg $7) in the formula in each instance.

=VLOOKUP(3,$A$7:$D$628,2,FALSE)

I would like to be able to replace the $7, $14, $21, etc with (7*(ROW()-2)) somehow. This calculation provides the right number for the Row reference, but I don't know how to get it to work WITHIN the cell reference part of the VLOOKUP function.

Does anyone have any ideas please?

Not completely sure what your trying to accomplish, if you can post an example of your data (Read This First: http://www.computing.net/howtos/sho... I think something using the =INDEX and/or the =INDIRECT function might get you what your looking for.

MIKE

Thanks Mike, I have received the answer I needed elsewhere, it was through the use of the =INDIRECT function that I was able to solve it..

Appreciate your response.Bob

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History