I'm sorry. I just don't get it. I tried:
=VLOOKUP(INDIRECT('List of Clients'!&(R&INT((ROW()+7)/8)+13&C1)),'List of
contacts'!R2C1:R4475C[38],2,FALSE)

I guess I don't understand INDIRECT very well. Can you step me through the
formula you wrote?

Thanks
--
Thank you,

scrowley(AT)littleonline.com


"Sheeloo" wrote:

Yes it does...

One more question...
If you use
=VLOOKUP('List of Clients'!$A$2,'List of contacts'!$A$2:AM$4475,2,FALSE) 14
times you will get the same value back every time... the first match for A2

Replace 'List of Clients'!R14C1 in your formula with
=INDIRECT("'List of Clients'!"&("R"&INT((ROW()+7)/8)+13&"C1"))

assuming you start at row1..

"Sandy Crowley" wrote:

Sheeloo,

Thanks for your reply. I have Use the R1C1 turned on in my Excel Options.
This is the formula without the R1C1:
=VLOOKUP('List of Clients'!$A$2,'List of contacts'!$A$2:AM$4475,2,FALSE)

Ideally, 8 cells in the same column will have
=VLOOKUP('List of Clients'!$A$2,'List of contacts'!$A$2:AM$4475,2,FALSE)
and the next 8 cells in the same column will have
=VLOOKUP('List of Clients'!$A$3,'List of contacts'!$A$2:AM$4475,2,FALSE)

The only thing that changes is $A$2 to $A$3 then $A$4, etc. Does this clarify?
--
Thank you,

scrowley(AT)littleonline.com


"Sheeloo" wrote:

Have you give R1C1 reference for illustration or is it a real formula?

What you want can be achieved by combining
="R"&INT((ROW()+7)/8)+13&"C1" with INDIRECT
The formula above if entered in Row 1 and copied down will give you
R14C1 for 14 rows then R15C1 for 14 rows and so on..

If you give your correct VLOOKUP formula then I can build the formula for you
"Sandy Crowley" wrote:

I have a forumla where I'm looking up a Client name and finding all the
contacts that relate to that Client. The most contacts per Client is 8. This
formula works great, I just need it to reference R14C1 for 8 rows and then
increase to R15C1 for 8 rows, etc. I have 562 unique Clients x 8 =4496 and
I'm sure there is a way to Fill Down this formula instead of pasting it 4496
times.

Thank you!

=VLOOKUP('List of Clients'!R14C1,'List of contacts'!R2C1:R4475C[38],2,FALSE)
--
Thank you,

scrowley(AT)littleonline.com