VLOOKUP formula that will relatively adjust rows by a block of 8 rows with absolute column

I have numerous blocks of data in 2 columns and 8 rows, and within each block I need to do repeated V-lookups to arrange the data in a different order on 4 separate spreadsheets (other workbooks). The columns are absolute (A & B), but I am constantly adding new rows in blocks of 8. Using a relative reference on the rows only adjusts them by one row. Is there a way to get them to adjust by 8 rows?

Each cell in the first column contains 3 letters of text that are unique within each block, but repeat again in each new block, although the order varies based on the corresponding numeric value in column B (in descending order).

I don't know if there is a formula for that, or a way to assign range names automatically, or set up tables or arrays, or some kind of macro to do this? The new data comes in by date, so I would consider using a third column with dates to trigger a new range name. (There is daily data on one sheet, weekly data on one sheet, monthly data on one sheet, etc. so even the dates will be tricky to use as range names since some data will overlap.)

I would like to automate this process as much as possible. Suggestions would be greatly appreciated!

Stevie the Kid

Can you post a sample workbook using non-confidential data, but in exactly the same layout as your real sheet.

You can make the row relative in blocks using a combination of index, row and floor / ceiling functions. (the sample layout will make it easier to factor in any adjustments needed).

There should be an Excel attachment here showing sample source data and the desired result. Note that the same text appears in column A within each block of 8 rows, and the order varies in relation to the corresponding values in column B, which are listed in descending order within each 8 row block.

The desired result was achieved using a V-lookup formula, however currently the source data range needs to be entered manually each time as the formula must shift to each new block of 8 rows for each new row of sorted data, for each new date, as new data is added. (This sorted data then needs to go to 4 other spreadsheets.)

I would like to somehow automate this process so that each new range doesn't have to be entered manually but will automatically adjust for each new block of 8 rows. I am open to suggestions on how to do this.

Jason.b75, I am not familiar with using index, row and floor / ceiling functions, but I am intrigued and interested to hear how this might work in this situation.

If I can provide further clarification, please let me know. Thanks so much for any help provided.

Enter the formula in A22, then copy right and down

=VLOOKUP(A$21,INDEX($A:$A,(ROWS(A$22:A22)-1)*8+2):INDEX($B:$B,ROWS(A$22:A22)*8+1),2,0)

Originally Posted by jason.b75

Personal preference is to start with the cell holding the first formula, using a different range will cause problems if rows are inserted / deleted.

edit:

A simple test in a blank worksheet

In A22 enter =ROWS(A$22:A22) and in B22 enter =ROWS(A$1:A1)

Copy both down for 10 rows. Delete row 2 (entire row) and insert a new row at row 5.

Now compare the 2 columns, in column B, 1 is duplicated, 5 is missing, in column A nothing has changed.

Wow, this was very enlightening and you gave a great example! So good to know your formula will work even if rows are deleted or inserted! Thanks so much for sharing this, I learned something. (Now can you show me the syntax for referring to a different sheet for the source data versus the desired results? Would the sheet name go after INDEX, as in INDEX,Sheet 1!, etc.? And is the name of the workbook also required?)

Pl see the attached file.
As per the desired results shown in your sheet, Array formula is in Cell D5.It can be dragged.
Any clarification welcome.

Originally Posted by kvsrinivasamurthy

Array formula is in Cell D5.It can be dragged.
Any clarification welcome.

The only downside to that would be the number of calculations required in the array, try that with a few hundred records and you'll have to wait an age for it to calculate.

Thank you to everyone who replied; this is tremendous information and I plan to incorporate it as soon as possible, (without deleting any rows)! And to jason.b75, this worked perfectly in the example! However, I should have shown that I will need to refer to a different worksheet for the source data, and I am not familiar enough with the syntax or with what this formula is actually doing to adjust it to reference a separate sheet. (I apologize that my example should have shown that.)

For instance if rows A21:H23 were on Sheet 2 in B2:I3, how would that syntax look to refer to the data on Sheet 1?

If anyone could help me out with this that would be amazingly awesome!

Wow, yes, this works perfectly! A huge thanks to all who wrote in and special thanks to jason.b75, martindwilson and oeldere for your help and input! I not only got a formula that did exactly what I needed, but I also learned something new. This will save me a ton of time in the future. Many thanks and I hope to work with you all again.

Stevie the Kid