Altering a match/index formula

KollBrian

Board Regular
Joined
Jul 8, 2002
Messages
58
Hey all, I need to alter an index/match formula to work with rows instead of columns.

Here is the original formula:
=INDEX('Loads'!AL3:AL185,MATCH(A3,'Loads'!A3:A185,FALSE),1)

below are the 2 examples of where I need to put the new formula and where it needs to look.
Month End Compilation Report.xls
ABCD
45DaveCrosby'sTeamTotalLoadsPickedUpYesterdayTotalPickupsLateYesterdayPickupOntime%
46
47Benefiel,Krissa110.0%
48Bowker,Dave110.0%
49Briceno,Alma110.0%
50Crooks,Holly110.0%
Printable Report
Month End Compilation Report.xls
ABCDEFG
81AbricenoAbricenoAcontrerasAcontrerasAoverbyAoverby
82PUDelPUDelPUDel
83158
84255
85347
86Totals6554785
Actual Late Loads


So, what I am trying to do is match the name in column "I" (the abreviated name) and the determining code (i.e. "pu" or "del") with the name and determining code in another worksheet, then give the total at the bottom of the sheet.

So that if the formula is for the loads picking up the formula looks for abriceno, then pu, then gives the total at the bottom of that column.....???

I am going to start hammering at this trying to see if I can figure it out, maybe a dual match... not sure yet, but if any of you have any thoughts, let me know.

bk
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Just an update, I am tampering with this formula at the moment, it at least is taking, but I get the #Value error so something is still not right.

=SUMPRODUCT(('Load'!$A$1:$DD$1=$I47)*('Load'!$A$2:$DD$2="pu")*('Load'!$A$73:$DD$73))
 
Upvote 0
Wow, it is rare when I can stump this entire group.

Just another update, so far I have not been able to get a formula to give me the desired results. The updated formula I was trying, would finally give me a result, but a result of "1", not the actual number.

So its back to the design board, ugh....

bk
 
Upvote 0
Max,

I dont understand, the ranges need to be the same size?

in this example I am trying to search range A (a2:dd2) then find the qualifier (pu or del) one row below, then give the result of the sum at the bottom (row 74) of the column.

How can I make the ranges the same size?

bk
This message was edited by KollBrian on 2002-09-05 00:43
 
Upvote 0
From the help files:

The array arguments must have the same dimensions. If they do not, SUMPRODUCT returns the #VALUE! error value.
 
Upvote 0
Max, Everyone,

Ok, what if I give up on the trying to search/match 2 criterium, and simply go with something like "=SUMPRODUCT(('CSR Load Count'!B1:DD1=$I47)*($B73:DD73>0))". I have made the 2 ranges the same size, but still dont get the total down below....

This presumes I change the persons name to name1, and name2 instead of a1=name, b1=pu, a2=name, b2=Del.

I am starting to get scrambled I think. Maybe I have finally reached the limit of excel....

bk
 
Upvote 0
Brian

Are you trying to sum the totals of PU and DEL or just count the PU and DEL? If you're summing you're missing an argument to sum what range.
 
Upvote 0
I am trying to either sum the column or copy the result of row 74 for that column (which is a sum formula).

The hard part is i need it to look in row 1 for the name (there are 2 identical listings per person) then when it finds the name I need it to look in row 2 for the "qualifier" (a word either "pu" or "del") and then give me the sum of that particular column.

That is the hard part, I can match when its in column format but I cant seem to do it in row.

For instance if the names were in column a, the qaulifiers in column b, and the quantity was in c:dd, I think I would have no trouble.

Its the fact that the "if" statements are in rows that seems to be causing the problem.

bk
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,142
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top