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

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

KollBrian

Board Regular
Joined
Jul 8, 2002
Messages
58
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))
 

KollBrian

Board Regular
Joined
Jul 8, 2002
Messages
58
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
 

KollBrian

Board Regular
Joined
Jul 8, 2002
Messages
58

ADVERTISEMENT

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
 

maxflia10

Well-known Member
Joined
May 20, 2002
Messages
890
From the help files:

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

KollBrian

Board Regular
Joined
Jul 8, 2002
Messages
58

ADVERTISEMENT

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
 

maxflia10

Well-known Member
Joined
May 20, 2002
Messages
890
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.
 

KollBrian

Board Regular
Joined
Jul 8, 2002
Messages
58
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
 

Forum statistics

Threads
1,147,673
Messages
5,742,530
Members
423,736
Latest member
dracula cyrus

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
Top