ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Index Function Problem (https://www.excelbanter.com/excel-worksheet-functions/251792-index-function-problem.html)

indyjojo
Index Function Problem
 
I need a function that will return all information for "truck 1" on another
worksheet. The information below is an example of information on my
worksheet. I need a function that will search the data and return all
instances of "truck 1" on a separate worksheet.

B C D E
F
Grade Mill Driver Weight Rate

1 ZONE 2 BATSON WILSON 25.00
2 ZONE 3 CAMDEN TROY 26.00
3 ZONE 4 CLW TRUCK 1 27.00 7
4 18 - 20 top CORRIGAN TRUCK 2 28.00 8
5 Dead Logs EVADALE TRUCK 3 29.00 9
6 PW GP WILSON 30.00
7 CNS LUFKIN TROY 31.00
8 HW Logs NAPCO TRUCK 1 32.00 10
9 ZONE 2 STONEHAM TRUCK 2 33.00 11
10 ZONE 3 BATSON WILSON 34.00 12
11 ZONE 4 CAMDEN WILSON 35.00
12 18 - 20 top CLW TROY 36.00
13 Dead Logs CORRIGAN TRUCK 1 37.00 13
14 PW EVADALE TRUCK 2 38.00 14
15 CNS GP TRUCK 3 39.00 7

I have an idex function on a separate worksheet and it works for the first
line but I need a function that will move to the second line and return the
next instance until all instances are returned so I can calculate pay for the
driver. Here's what I have:
=INDEX(LEASE_DRIVERPAY,MATCH("TRUCK 1",DRIVER_NAME,0),1)
=INDEX(LEASE_DRIVERPAY,MATCH("TRUCK 1",DRIVER_NAME,0),3)
=INDEX(LEASE_DRIVERPAY,MATCH("TRUCK 1",DRIVER_NAME,0),4)

Please help!

T. Valko
Index Function Problem
 
Try this...

Sheet2 A1 = Truck 1

Enter this formula on Sheet2 B1:

=COUNTIF(DRIVER_NAME,A1)

Enter this array formula** on Sheet2 A2:

=IF(ROWS(A$2:A2)B$1,"",INDEX(LEASE_DRIVERPAY,SMAL L(IF(DRIVER_NAME=A$1,ROW(LEASE_DRIVERPAY)),ROWS(A$ 2:A2))-MIN(ROW(LEASE_DRIVERPAY))+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Copy down until you get blanks.

--
Biff
Microsoft Excel MVP


"indyjojo" wrote in message
...
I need a function that will return all information for "truck 1" on another
worksheet. The information below is an example of information on my
worksheet. I need a function that will search the data and return all
instances of "truck 1" on a separate worksheet.

B C D E
F
Grade Mill Driver Weight Rate

1 ZONE 2 BATSON WILSON 25.00
2 ZONE 3 CAMDEN TROY 26.00
3 ZONE 4 CLW TRUCK 1 27.00 7
4 18 - 20 top CORRIGAN TRUCK 2 28.00 8
5 Dead Logs EVADALE TRUCK 3 29.00 9
6 PW GP WILSON 30.00
7 CNS LUFKIN TROY 31.00
8 HW Logs NAPCO TRUCK 1 32.00 10
9 ZONE 2 STONEHAM TRUCK 2 33.00 11
10 ZONE 3 BATSON WILSON 34.00 12
11 ZONE 4 CAMDEN WILSON 35.00
12 18 - 20 top CLW TROY 36.00
13 Dead Logs CORRIGAN TRUCK 1 37.00 13
14 PW EVADALE TRUCK 2 38.00 14
15 CNS GP TRUCK 3 39.00 7

I have an idex function on a separate worksheet and it works for the first
line but I need a function that will move to the second line and return
the
next instance until all instances are returned so I can calculate pay for
the
driver. Here's what I have:
=INDEX(LEASE_DRIVERPAY,MATCH("TRUCK 1",DRIVER_NAME,0),1)
=INDEX(LEASE_DRIVERPAY,MATCH("TRUCK 1",DRIVER_NAME,0),3)
=INDEX(LEASE_DRIVERPAY,MATCH("TRUCK 1",DRIVER_NAME,0),4)

Please help!



indyjojo
Index Function Problem
 
Thanks so much for your time. I got the blanks but the first five lines
return an error. I changed the range names to what I thought would give me
the mill name but instead I got "truck 1". I changed the formula to this:

=IF(ROWS(A$5:A5)B$1,"",INDEX(DRIVER_NAME,SMALL(IF (DRIVER_NAME=A$1,ROW(MILL_NAME)),ROWS(A$5:A5))-MIN(ROW(MILL_NAME))+1))

The range names are as follows:

colC Mill_Name
colD Driver_Name
colC:F Lease_Driverpay

All the information in the range name Lease_Driverpay needs to go over to
worksheet 2 but only for truck 1. Once I get a functional formula, can this
be accomplished by copying the formula to the right? or will I have to do a
different formula for each column?

Again thank you for your time and assistance.

"T. Valko" wrote:

Try this...

Sheet2 A1 = Truck 1

Enter this formula on Sheet2 B1:

=COUNTIF(DRIVER_NAME,A1)

Enter this array formula** on Sheet2 A2:

=IF(ROWS(A$2:A2)B$1,"",INDEX(LEASE_DRIVERPAY,SMAL L(IF(DRIVER_NAME=A$1,ROW(LEASE_DRIVERPAY)),ROWS(A$ 2:A2))-MIN(ROW(LEASE_DRIVERPAY))+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Copy down until you get blanks.

--
Biff
Microsoft Excel MVP


"indyjojo" wrote in message
...
I need a function that will return all information for "truck 1" on another
worksheet. The information below is an example of information on my
worksheet. I need a function that will search the data and return all
instances of "truck 1" on a separate worksheet.

B C D E
F
Grade Mill Driver Weight Rate

1 ZONE 2 BATSON WILSON 25.00
2 ZONE 3 CAMDEN TROY 26.00
3 ZONE 4 CLW TRUCK 1 27.00 7
4 18 - 20 top CORRIGAN TRUCK 2 28.00 8
5 Dead Logs EVADALE TRUCK 3 29.00 9
6 PW GP WILSON 30.00
7 CNS LUFKIN TROY 31.00
8 HW Logs NAPCO TRUCK 1 32.00 10
9 ZONE 2 STONEHAM TRUCK 2 33.00 11
10 ZONE 3 BATSON WILSON 34.00 12
11 ZONE 4 CAMDEN WILSON 35.00
12 18 - 20 top CLW TROY 36.00
13 Dead Logs CORRIGAN TRUCK 1 37.00 13
14 PW EVADALE TRUCK 2 38.00 14
15 CNS GP TRUCK 3 39.00 7

I have an idex function on a separate worksheet and it works for the first
line but I need a function that will move to the second line and return
the
next instance until all instances are returned so I can calculate pay for
the
driver. Here's what I have:
=INDEX(LEASE_DRIVERPAY,MATCH("TRUCK 1",DRIVER_NAME,0),1)
=INDEX(LEASE_DRIVERPAY,MATCH("TRUCK 1",DRIVER_NAME,0),3)
=INDEX(LEASE_DRIVERPAY,MATCH("TRUCK 1",DRIVER_NAME,0),4)

Please help!


.

T. Valko
Index Function Problem
 
What version of Excel are you using?

Unless you're using Excel 2007 your named range can't refer to entire
columns and be used in an array formula.

--
Biff
Microsoft Excel MVP


"indyjojo" wrote in message
...
Thanks so much for your time. I got the blanks but the first five lines
return an error. I changed the range names to what I thought would give me
the mill name but instead I got "truck 1". I changed the formula to this:

=IF(ROWS(A$5:A5)B$1,"",INDEX(DRIVER_NAME,SMALL(IF (DRIVER_NAME=A$1,ROW(MILL_NAME)),ROWS(A$5:A5))-MIN(ROW(MILL_NAME))+1))

The range names are as follows:

colC Mill_Name
colD Driver_Name
colC:F Lease_Driverpay

All the information in the range name Lease_Driverpay needs to go over to
worksheet 2 but only for truck 1. Once I get a functional formula, can
this
be accomplished by copying the formula to the right? or will I have to do
a
different formula for each column?

Again thank you for your time and assistance.

"T. Valko" wrote:

Try this...

Sheet2 A1 = Truck 1

Enter this formula on Sheet2 B1:

=COUNTIF(DRIVER_NAME,A1)

Enter this array formula** on Sheet2 A2:

=IF(ROWS(A$2:A2)B$1,"",INDEX(LEASE_DRIVERPAY,SMAL L(IF(DRIVER_NAME=A$1,ROW(LEASE_DRIVERPAY)),ROWS(A$ 2:A2))-MIN(ROW(LEASE_DRIVERPAY))+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

Copy down until you get blanks.

--
Biff
Microsoft Excel MVP


"indyjojo" wrote in message
...
I need a function that will return all information for "truck 1" on
another
worksheet. The information below is an example of information on my
worksheet. I need a function that will search the data and return all
instances of "truck 1" on a separate worksheet.

B C D E
F
Grade Mill Driver Weight
Rate

1 ZONE 2 BATSON WILSON 25.00
2 ZONE 3 CAMDEN TROY 26.00
3 ZONE 4 CLW TRUCK 1 27.00 7
4 18 - 20 top CORRIGAN TRUCK 2 28.00 8
5 Dead Logs EVADALE TRUCK 3 29.00 9
6 PW GP WILSON 30.00
7 CNS LUFKIN TROY 31.00
8 HW Logs NAPCO TRUCK 1 32.00 10
9 ZONE 2 STONEHAM TRUCK 2 33.00 11
10 ZONE 3 BATSON WILSON 34.00 12
11 ZONE 4 CAMDEN WILSON 35.00
12 18 - 20 top CLW TROY 36.00
13 Dead Logs CORRIGAN TRUCK 1 37.00 13
14 PW EVADALE TRUCK 2 38.00 14
15 CNS GP TRUCK 3 39.00 7

I have an idex function on a separate worksheet and it works for the
first
line but I need a function that will move to the second line and return
the
next instance until all instances are returned so I can calculate pay
for
the
driver. Here's what I have:
=INDEX(LEASE_DRIVERPAY,MATCH("TRUCK 1",DRIVER_NAME,0),1)
=INDEX(LEASE_DRIVERPAY,MATCH("TRUCK 1",DRIVER_NAME,0),3)
=INDEX(LEASE_DRIVERPAY,MATCH("TRUCK 1",DRIVER_NAME,0),4)

Please help!


.

macropod[_2_]
Index Function Problem
 
Hi indyjojo,

For some working examples, see the attachments to my posts at:
http://www.techsupportforum.com/micr...la-issues.html

--
Cheers
macropod
[Microsoft MVP - Word]


"indyjojo" wrote in message ...
I need a function that will return all information for "truck 1" on another
worksheet. The information below is an example of information on my
worksheet. I need a function that will search the data and return all
instances of "truck 1" on a separate worksheet.

B C D E
F
Grade Mill Driver Weight Rate

1 ZONE 2 BATSON WILSON 25.00
2 ZONE 3 CAMDEN TROY 26.00
3 ZONE 4 CLW TRUCK 1 27.00 7
4 18 - 20 top CORRIGAN TRUCK 2 28.00 8
5 Dead Logs EVADALE TRUCK 3 29.00 9
6 PW GP WILSON 30.00
7 CNS LUFKIN TROY 31.00
8 HW Logs NAPCO TRUCK 1 32.00 10
9 ZONE 2 STONEHAM TRUCK 2 33.00 11
10 ZONE 3 BATSON WILSON 34.00 12
11 ZONE 4 CAMDEN WILSON 35.00
12 18 - 20 top CLW TROY 36.00
13 Dead Logs CORRIGAN TRUCK 1 37.00 13
14 PW EVADALE TRUCK 2 38.00 14
15 CNS GP TRUCK 3 39.00 7

I have an idex function on a separate worksheet and it works for the first
line but I need a function that will move to the second line and return the
next instance until all instances are returned so I can calculate pay for the
driver. Here's what I have:
=INDEX(LEASE_DRIVERPAY,MATCH("TRUCK 1",DRIVER_NAME,0),1)
=INDEX(LEASE_DRIVERPAY,MATCH("TRUCK 1",DRIVER_NAME,0),3)
=INDEX(LEASE_DRIVERPAY,MATCH("TRUCK 1",DRIVER_NAME,0),4)

Please help!

indyjojo
Index Function Problem
 
2003

"T. Valko" wrote:

What version of Excel are you using?

Unless you're using Excel 2007 your named range can't refer to entire
columns and be used in an array formula.

--
Biff
Microsoft Excel MVP


"indyjojo" wrote in message
...
Thanks so much for your time. I got the blanks but the first five lines
return an error. I changed the range names to what I thought would give me
the mill name but instead I got "truck 1". I changed the formula to this:

=IF(ROWS(A$5:A5)B$1,"",INDEX(DRIVER_NAME,SMALL(IF (DRIVER_NAME=A$1,ROW(MILL_NAME)),ROWS(A$5:A5))-MIN(ROW(MILL_NAME))+1))

The range names are as follows:

colC Mill_Name
colD Driver_Name
colC:F Lease_Driverpay

All the information in the range name Lease_Driverpay needs to go over to
worksheet 2 but only for truck 1. Once I get a functional formula, can
this
be accomplished by copying the formula to the right? or will I have to do
a
different formula for each column?

Again thank you for your time and assistance.

"T. Valko" wrote:

Try this...

Sheet2 A1 = Truck 1

Enter this formula on Sheet2 B1:

=COUNTIF(DRIVER_NAME,A1)

Enter this array formula** on Sheet2 A2:

=IF(ROWS(A$2:A2)B$1,"",INDEX(LEASE_DRIVERPAY,SMAL L(IF(DRIVER_NAME=A$1,ROW(LEASE_DRIVERPAY)),ROWS(A$ 2:A2))-MIN(ROW(LEASE_DRIVERPAY))+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

Copy down until you get blanks.

--
Biff
Microsoft Excel MVP


"indyjojo" wrote in message
...
I need a function that will return all information for "truck 1" on
another
worksheet. The information below is an example of information on my
worksheet. I need a function that will search the data and return all
instances of "truck 1" on a separate worksheet.

B C D E
F
Grade Mill Driver Weight
Rate

1 ZONE 2 BATSON WILSON 25.00
2 ZONE 3 CAMDEN TROY 26.00
3 ZONE 4 CLW TRUCK 1 27.00 7
4 18 - 20 top CORRIGAN TRUCK 2 28.00 8
5 Dead Logs EVADALE TRUCK 3 29.00 9
6 PW GP WILSON 30.00
7 CNS LUFKIN TROY 31.00
8 HW Logs NAPCO TRUCK 1 32.00 10
9 ZONE 2 STONEHAM TRUCK 2 33.00 11
10 ZONE 3 BATSON WILSON 34.00 12
11 ZONE 4 CAMDEN WILSON 35.00
12 18 - 20 top CLW TROY 36.00
13 Dead Logs CORRIGAN TRUCK 1 37.00 13
14 PW EVADALE TRUCK 2 38.00 14
15 CNS GP TRUCK 3 39.00 7

I have an idex function on a separate worksheet and it works for the
first
line but I need a function that will move to the second line and return
the
next instance until all instances are returned so I can calculate pay
for
the
driver. Here's what I have:
=INDEX(LEASE_DRIVERPAY,MATCH("TRUCK 1",DRIVER_NAME,0),1)
=INDEX(LEASE_DRIVERPAY,MATCH("TRUCK 1",DRIVER_NAME,0),3)
=INDEX(LEASE_DRIVERPAY,MATCH("TRUCK 1",DRIVER_NAME,0),4)

Please help!


.



.

indyjojo
Index Function Problem
 
Thanks for all your help. Hours later, I finally got it to work.

"T. Valko" wrote:

What version of Excel are you using?

Unless you're using Excel 2007 your named range can't refer to entire
columns and be used in an array formula.

--
Biff
Microsoft Excel MVP


"indyjojo" wrote in message
...
Thanks so much for your time. I got the blanks but the first five lines
return an error. I changed the range names to what I thought would give me
the mill name but instead I got "truck 1". I changed the formula to this:

=IF(ROWS(A$5:A5)B$1,"",INDEX(DRIVER_NAME,SMALL(IF (DRIVER_NAME=A$1,ROW(MILL_NAME)),ROWS(A$5:A5))-MIN(ROW(MILL_NAME))+1))

The range names are as follows:

colC Mill_Name
colD Driver_Name
colC:F Lease_Driverpay

All the information in the range name Lease_Driverpay needs to go over to
worksheet 2 but only for truck 1. Once I get a functional formula, can
this
be accomplished by copying the formula to the right? or will I have to do
a
different formula for each column?

Again thank you for your time and assistance.

"T. Valko" wrote:

Try this...

Sheet2 A1 = Truck 1

Enter this formula on Sheet2 B1:

=COUNTIF(DRIVER_NAME,A1)

Enter this array formula** on Sheet2 A2:

=IF(ROWS(A$2:A2)B$1,"",INDEX(LEASE_DRIVERPAY,SMAL L(IF(DRIVER_NAME=A$1,ROW(LEASE_DRIVERPAY)),ROWS(A$ 2:A2))-MIN(ROW(LEASE_DRIVERPAY))+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

Copy down until you get blanks.

--
Biff
Microsoft Excel MVP


"indyjojo" wrote in message
...
I need a function that will return all information for "truck 1" on
another
worksheet. The information below is an example of information on my
worksheet. I need a function that will search the data and return all
instances of "truck 1" on a separate worksheet.

B C D E
F
Grade Mill Driver Weight
Rate

1 ZONE 2 BATSON WILSON 25.00
2 ZONE 3 CAMDEN TROY 26.00
3 ZONE 4 CLW TRUCK 1 27.00 7
4 18 - 20 top CORRIGAN TRUCK 2 28.00 8
5 Dead Logs EVADALE TRUCK 3 29.00 9
6 PW GP WILSON 30.00
7 CNS LUFKIN TROY 31.00
8 HW Logs NAPCO TRUCK 1 32.00 10
9 ZONE 2 STONEHAM TRUCK 2 33.00 11
10 ZONE 3 BATSON WILSON 34.00 12
11 ZONE 4 CAMDEN WILSON 35.00
12 18 - 20 top CLW TROY 36.00
13 Dead Logs CORRIGAN TRUCK 1 37.00 13
14 PW EVADALE TRUCK 2 38.00 14
15 CNS GP TRUCK 3 39.00 7

I have an idex function on a separate worksheet and it works for the
first
line but I need a function that will move to the second line and return
the
next instance until all instances are returned so I can calculate pay
for
the
driver. Here's what I have:
=INDEX(LEASE_DRIVERPAY,MATCH("TRUCK 1",DRIVER_NAME,0),1)
=INDEX(LEASE_DRIVERPAY,MATCH("TRUCK 1",DRIVER_NAME,0),3)
=INDEX(LEASE_DRIVERPAY,MATCH("TRUCK 1",DRIVER_NAME,0),4)

Please help!


.



.

T. Valko
Index Function Problem
 
Good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"indyjojo" wrote in message
...
Thanks for all your help. Hours later, I finally got it to work.

"T. Valko" wrote:

What version of Excel are you using?

Unless you're using Excel 2007 your named range can't refer to entire
columns and be used in an array formula.

--
Biff
Microsoft Excel MVP


"indyjojo" wrote in message
...
Thanks so much for your time. I got the blanks but the first five lines
return an error. I changed the range names to what I thought would give
me
the mill name but instead I got "truck 1". I changed the formula to
this:

=IF(ROWS(A$5:A5)B$1,"",INDEX(DRIVER_NAME,SMALL(IF (DRIVER_NAME=A$1,ROW(MILL_NAME)),ROWS(A$5:A5))-MIN(ROW(MILL_NAME))+1))

The range names are as follows:

colC Mill_Name
colD Driver_Name
colC:F Lease_Driverpay

All the information in the range name Lease_Driverpay needs to go over
to
worksheet 2 but only for truck 1. Once I get a functional formula, can
this
be accomplished by copying the formula to the right? or will I have to
do
a
different formula for each column?

Again thank you for your time and assistance.

"T. Valko" wrote:

Try this...

Sheet2 A1 = Truck 1

Enter this formula on Sheet2 B1:

=COUNTIF(DRIVER_NAME,A1)

Enter this array formula** on Sheet2 A2:

=IF(ROWS(A$2:A2)B$1,"",INDEX(LEASE_DRIVERPAY,SMAL L(IF(DRIVER_NAME=A$1,ROW(LEASE_DRIVERPAY)),ROWS(A$ 2:A2))-MIN(ROW(LEASE_DRIVERPAY))+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

Copy down until you get blanks.

--
Biff
Microsoft Excel MVP


"indyjojo" wrote in message
...
I need a function that will return all information for "truck 1" on
another
worksheet. The information below is an example of information on my
worksheet. I need a function that will search the data and return
all
instances of "truck 1" on a separate worksheet.

B C D E
F
Grade Mill Driver Weight
Rate

1 ZONE 2 BATSON WILSON 25.00
2 ZONE 3 CAMDEN TROY 26.00
3 ZONE 4 CLW TRUCK 1 27.00 7
4 18 - 20 top CORRIGAN TRUCK 2 28.00 8
5 Dead Logs EVADALE TRUCK 3 29.00 9
6 PW GP WILSON 30.00
7 CNS LUFKIN TROY 31.00
8 HW Logs NAPCO TRUCK 1 32.00 10
9 ZONE 2 STONEHAM TRUCK 2 33.00 11
10 ZONE 3 BATSON WILSON 34.00 12
11 ZONE 4 CAMDEN WILSON 35.00
12 18 - 20 top CLW TROY 36.00
13 Dead Logs CORRIGAN TRUCK 1 37.00 13
14 PW EVADALE TRUCK 2 38.00 14
15 CNS GP TRUCK 3 39.00 7

I have an idex function on a separate worksheet and it works for the
first
line but I need a function that will move to the second line and
return
the
next instance until all instances are returned so I can calculate
pay
for
the
driver. Here's what I have:
=INDEX(LEASE_DRIVERPAY,MATCH("TRUCK 1",DRIVER_NAME,0),1)
=INDEX(LEASE_DRIVERPAY,MATCH("TRUCK 1",DRIVER_NAME,0),3)
=INDEX(LEASE_DRIVERPAY,MATCH("TRUCK 1",DRIVER_NAME,0),4)

Please help!


.



.


All times are GMT +1. The time now is 02:49 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com