Copying forumla for vlook up but changing the column Index #

[Roger Govier]

Hi Biff

For the sake of completeness, I can report the following from the files
that Mary mailed to me.
Whilst there were 22 lines to be completed for each payslip, they did
not form a contiguous series from 8 to 30 as we might have imagined.
Some were repeats of data from the same position in the lookup table.
Some were based upon calculations of the results from a few of the
values picked up from the lookup table.
Therefore, there was no way that creating a series of increasing lookups
using the Row() function would have worked.

Fortunately, there was a column of data on the results table called Pay
Field-Number. Whilst this did not correspond to the numerical order of
data in the lookup table, I was able to utilise it.
I inserted an extra row in the lookup table and "mapped" these field
numbers to the respective columns.
I also extended the lookup table by 5 columns, and in these cells
carried out the calculations that were required for inclusion in the
results table.
Then a combination of Index() Match() permitted a single formula to be
replicated down the 23,000 lines of the results table.

--
Regards

Roger Govier

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

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

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


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

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.



..
:

Try this:

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

Biff

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)

 

[Biff]

I was hoping that you would post your findings.

Biff

Hi Biff

For the sake of completeness, I can report the following from the files
that Mary mailed to me.
Whilst there were 22 lines to be completed for each payslip, they did not
form a contiguous series from 8 to 30 as we might have imagined.
Some were repeats of data from the same position in the lookup table.
Some were based upon calculations of the results from a few of the values
picked up from the lookup table.
Therefore, there was no way that creating a series of increasing lookups
using the Row() function would have worked.

Fortunately, there was a column of data on the results table called Pay
Field-Number. Whilst this did not correspond to the numerical order of
data in the lookup table, I was able to utilise it.
I inserted an extra row in the lookup table and "mapped" these field
numbers to the respective columns.
I also extended the lookup table by 5 columns, and in these cells carried
out the calculations that were required for inclusion in the results
table.
Then a combination of Index() Match() permitted a single formula to be
replicated down the 23,000 lines of the results table.

--
Regards

Roger Govier

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

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

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


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

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.



..
:

Try this:

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

Biff

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)