U r understanding it correctly, but to make it clearer, in the spreadsheet I
am pulling it from there is one row with one check # with several columns.
However in the spreadshett I am pulling the information in the data for every
column must be in a separate rown and the check # is repeated on every row
for 23 row. So in Spreadsheet Paytst50-mod.xls the check # is listed in
a2..a1522, but in Payroll import.xls the check # for a2 is listed 23 times
then it pulls the ck # for a2 from Paytst50-mod.xls 23 more times but the
data is pulled from column 8-20, (column Index postion from spreadsheet
Paytst50-mod.xls , then I calulte a formula for few lines, which I may need
help with later on formult repeating. This file is being imported into a
..csv file. After I get the information pulled in, then I will copy and paste
values only. Does this make it clearer.

Thanks for all the help we are getting closer and sorry I fell a sleep,
wished I had been up when u guess were doing working this out.

"Biff" wrote:

BTW,

it has 23,000 lines. The formula is on all lines

I wouldn't use an incrementing Countif on 23,000 rows even though it would
work! I'd use something like you did but I didn't know what to calculate for
based on my understanding so I just suggested what I did to "coax" a
response from the OP.

Biff

"Biff" wrote in message
...
Hmmm....

The way I read it is......

I have 23 lines per payroll check per employee
Example Check # 1 is A2-A23, Check #2 is a24-A46 , check # 3 is A47-A69

Each lookup value is listed 22 (or 23 ?) times therefore needs 22 (or 23
?) column_index_numbers. So, if you start at 8 and increment for 21 (or 22
?) more lookup_values then you're outside the lookup_table.

Biff

"Roger Govier" wrote in message
...
Hi Biff

No it is not incrementing by 23 rows from 8 (which I agree would take it
outside of the table) it incrementing from 8 to 23 then back to 8 again.

I think that your original posting modified to
=VLOOKUP(A$2,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,MOD(ROW(A16),16)+8,0)
will cycle through the necessary range 8 to 23 repeatedly.

--
Regards

Roger Govier


"Biff" wrote in message
...
If you start with column 8 and increment it for 23 rows then that takes
you outside of your lookup table - $A$2:$Y$1522.

My guess is you want something like this:

=VLOOKUP(A5,A$1:F$3,COUNTIF(A$5:A5,A5)+1,0)

The column number will start at 2 and increment 1 for each instance of
the lookup value and will restart with a new lookup value.

Biff

"klafert" wrote in message
...
This worked, the only problem is that after the 23 line I need it to
look for
the next check number. Example Check # 1 is A2-A23, Check #2 is
a24-A46 ,
check # 3 is A47-A69. I guess I should have pasted my example as
follows:

=VLOOKUP(A2,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,8,0)
=VLOOKUP(A3,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,9,0)
=VLOOKUP(A4,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,10,0)

The 24th line would be:

=VLOOKUP(A24,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,8,0)
and the 47th line would be:

=VLOOKUP(A47,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,8,0)

Other than starting the formula over every 23 lines it worked great.



..
"Biff" wrote:

Try this:

=VLOOKUP(A$2,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,ROW(A8),0)

Biff

"klafert" wrote in message
...
I have a spreadsheet that I use the vlookup command. However, I have
23
lines per payroll check per employee - I am getting the data I want.
However
it has 23,000 lines. The formula is on all lines but the column
Index #
chaning to pull the correct information. Is there a way to copy
this
forumla
but have it change the column Index # automatically, instead if me
manually
change each column when necessary. Example below - hopefully this
will
clear
up any confusion.

=VLOOKUP(A2,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,8,0)
=VLOOKUP(A2,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,9,0)
=VLOOKUP(A2,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,10,0)

=VLOOKUP(A2,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,11,0)