Index Function Problem

[indyjojo]

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]

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,SMALL(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.

 

[indyjojo]

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]

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.

 

[macropod]

Hi indyjojo,

For some working examples, see the attachments to my posts at:
http://www.techsupportforum.com/mic...fice-support/375768-excel-formula-issues.html

 

[indyjojo]

2003

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]

Thanks for all your help. Hours later, I finally got it to work.

 

[T. Valko]

Good deal. Thanks for the feedback!