Index & Match

G

Geger

New Member
Joined
Aug 8, 2018
Messages
3
Hello - can you help me with index & match formula. I've tried many times but I can't get it :eeek:

I have a large data set that I need to update week as week go by so I need to do an index/ match to return information on another workbook automatically as I refresh the weekly dates.

[TABLE="width: 1088"]
<colgroup><col width="64" span="17" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]TYPE[/TD]
[TD="width: 64"]Size[/TD]
[TD="class: xl65, width: 64, align: right"]9-Jul[/TD]
[TD="class: xl65, width: 64, align: right"]16-Jul[/TD]
[TD="class: xl65, width: 64, align: right"]23-Jul[/TD]
[TD="class: xl65, width: 64, align: right"]30-Jul[/TD]
[TD="class: xl65, width: 64, align: right"]6-Aug[/TD]
[TD="class: xl65, width: 64, align: right"]13-Aug[/TD]
[TD="class: xl65, width: 64, align: right"]20-Aug[/TD]
[TD="class: xl65, width: 64, align: right"]27-Aug[/TD]
[TD="class: xl65, width: 64, align: right"]3-Sep[/TD]
[TD="class: xl65, width: 64, align: right"]10-Sep[/TD]
[TD="class: xl65, width: 64, align: right"]17-Sep[/TD]
[TD="class: xl65, width: 64, align: right"]24-Sep[/TD]
[TD="class: xl65, width: 64, align: right"]1-Oct[/TD]
[TD="class: xl65, width: 64, align: right"]8-Oct[/TD]
[TD="class: xl65, width: 64, align: right"]15-Oct[/TD]
[/TR]
[TR]
[TD]BLUE[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BLUE[/TD]
[TD="align: right"]4.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BLUE[/TD]
[TD="align: right"]1.5[/TD]
[TD][/TD]
[TD="align: right"]565[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]YELLOW[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]143[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]YELLOW[/TD]
[TD="align: right"]4.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]YELLOW[/TD]
[TD="align: right"]1.5[/TD]
[TD][/TD]
[TD="align: right"]234[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 1329"]
<colgroup><col><col span="19"></colgroup><tbody>[TR]
[TD]TYPE[/TD]
[TD]Size[/TD]
[TD="align: right"]9-Jul[/TD]
[TD="align: right"]16-Jul[/TD]
[TD="align: right"]23-Jul[/TD]
[TD="align: right"]30-Jul[/TD]
[TD="align: right"]6-Aug[/TD]
[TD="align: right"]13-Aug[/TD]
[TD="align: right"]20-Aug[/TD]
[TD="align: right"]27-Aug[/TD]
[TD="align: right"]3-Sep[/TD]
[TD="align: right"]10-Sep[/TD]
[TD="align: right"]17-Sep[/TD]
[TD="align: right"]24-Sep[/TD]
[TD="align: right"]1-Oct[/TD]
[TD="align: right"]8-Oct[/TD]
[TD="align: right"]15-Oct[/TD]
[TD="align: right"]22-Oct[/TD]
[TD="align: right"]29-Oct[/TD]
[TD="align: right"]5-Nov[/TD]
[/TR]
[TR]
[TD]BLUE[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]1456[/TD]
[TD="align: right"]5656[/TD]
[TD="align: right"]5646[/TD]
[TD="align: right"]124[/TD]
[TD="align: right"]11292[/TD]
[TD="align: right"]8974[/TD]
[TD="align: right"]890[/TD]
[TD="align: right"]1456[/TD]
[TD="align: right"]8187[/TD]
[/TR]
[TR]
[TD]GREEN[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1836[/TD]
[TD="align: right"]587[/TD]
[TD="align: right"]2348[/TD]
[TD="align: right"]168[/TD]
[TD="align: right"]672[/TD]
[TD="align: right"]336[/TD]
[TD="align: right"]927[/TD]
[TD="align: right"]2556[/TD]
[TD="align: right"]7049[/TD]
[TD="align: right"]244[/TD]
[/TR]
[TR]
[TD]RED[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]199[/TD]
[TD="align: right"]153[/TD]
[TD="align: right"]612[/TD]
[TD="align: right"]144[/TD]
[TD="align: right"]576[/TD]
[TD="align: right"]288[/TD]
[TD="align: right"]794[/TD]
[TD="align: right"]2191[/TD]
[TD="align: right"]6042[/TD]
[TD="align: right"]209[/TD]
[/TR]
[TR]
[TD]YELLOW[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]143[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]1359[/TD]
[TD="align: right"]5436[/TD]
[TD="align: right"]388[/TD]
[TD="align: right"]1553[/TD]
[TD="align: right"]777[/TD]
[TD="align: right"]2142[/TD]
[TD="align: right"]5907[/TD]
[TD="align: right"]16292[/TD]
[TD="align: right"]563[/TD]
[/TR]
[TR]
[TD]YELLOW[/TD]
[TD="align: right"]4.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1405[/TD]
[TD="align: right"]164[/TD]
[TD="align: right"]656[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]187[/TD]
[TD="align: right"]94[/TD]
[TD="align: right"]258[/TD]
[TD="align: right"]713[/TD]
[TD="align: right"]1966[/TD]
[TD="align: right"]68[/TD]
[/TR]
[TR]
[TD]RED[/TD]
[TD="align: right"]4.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]546[/TD]
[TD="align: right"]2184[/TD]
[TD="align: right"]156[/TD]
[TD="align: right"]624[/TD]
[TD="align: right"]312[/TD]
[TD="align: right"]860[/TD]
[TD="align: right"]2373[/TD]
[TD="align: right"]6545[/TD]
[TD="align: right"]226[/TD]
[/TR]
[TR]
[TD]BLUE[/TD]
[TD="align: right"]4.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]675[/TD]
[TD="align: right"]597[/TD]
[TD="align: right"]2388[/TD]
[TD="align: right"]171[/TD]
[TD="align: right"]684[/TD]
[TD="align: right"]342[/TD]
[TD="align: right"]943[/TD]
[TD="align: right"]2601[/TD]
[TD="align: right"]7175[/TD]
[TD="align: right"]248[/TD]
[/TR]
[TR]
[TD]GREEN[/TD]
[TD="align: right"]4.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]130[/TD]
[TD="align: right"]1122[/TD]
[TD="align: right"]4488[/TD]
[TD="align: right"]321[/TD]
[TD="align: right"]1282[/TD]
[TD="align: right"]641[/TD]
[TD="align: right"]1768[/TD]
[TD="align: right"]4877[/TD]
[TD="align: right"]13450[/TD]
[TD="align: right"]465[/TD]
[/TR]
[TR]
[TD]BLUE[/TD]
[TD="align: right"]1.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]565[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]653[/TD]
[TD="align: right"]2612[/TD]
[TD="align: right"]187[/TD]
[TD="align: right"]748[/TD]
[TD="align: right"]374[/TD]
[TD="align: right"]1031[/TD]
[TD="align: right"]2845[/TD]
[TD="align: right"]7846[/TD]
[TD="align: right"]271[/TD]
[/TR]
[TR]
[TD]YELLOW[/TD]
[TD="align: right"]1.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]234[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]204[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]83[/TD]
[TD="align: right"]228[/TD]
[TD="align: right"]629[/TD]
[TD="align: right"]22[/TD]
[/TR]
[TR]
[TD]GREEN[/TD]
[TD="align: right"]1.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]846[/TD]
[TD="align: right"]4987[/TD]
[TD="align: right"]19948[/TD]
[TD="align: right"]1425[/TD]
[TD="align: right"]5700[/TD]
[TD="align: right"]2850[/TD]
[TD="align: right"]7860[/TD]
[TD="align: right"]2164[/TD]
[TD="align: right"]1435[/TD]
[TD="align: right"]2066[/TD]
[/TR]
[TR]
[TD]RED[/TD]
[TD="align: right"]1.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]785[/TD]
[TD="align: right"]3140[/TD]
[TD="align: right"]224[/TD]
[TD="align: right"]896[/TD]
[TD="align: right"]448[/TD]
[TD="align: right"]1236[/TD]
[TD="align: right"]3408[/TD]
[TD="align: right"]9399[/TD]
[TD="align: right"]325[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sort by date Sort by votes
Re: Help with Index & Match... not working at all!

In column U next to the second table (in my case, A11:T23), enter "=A11&B11". Copy it down to U23. This is a help column. If you don't want to see it, either hide it or set it at an obscure place so you won't see it.

In D4 (16-Jul, Blue, 1.5), enter the formula "=INDEX(D$12:D$23,MATCH($A4&$B4,$U$12:$U$23,0),MATCH(D$1,D$11:$T$11,0))". Copy and paste it to the rest of the table 1.
 
Last edited:
Upvote 0
Re: Help with Index & Match... not working at all!

Welcome To Board Geger!
Try following Formula to solve your problem. Copy and Past Formula in C11, Drag down and left ward according to your need. And reply me is it work for you.


Book1
ABCDEFGHIJKLMNOPQ
1TYPESize9-Jul16-Jul23-Jul30-Jul6-Aug13-Aug20-Aug27-Aug3-Sep10-Sep17-Sep24-Sep1-Oct8-Oct15-Oct
2Blue1565
3BLUE4.5565
4BLUE1.5565
5BLUE1.5143
6YELLOW1143
7YELLOW4.5234
8YELLOW1.5234
9
10TYPESize9-Jul16-Jul23-Jul30-Jul6-Aug13-Aug20-Aug27-Aug3-Sep10-Sep17-Sep24-Sep1-Oct8-Oct15-Oct
11Blue156500000000000000
12Green1000000000000000
13Red1000000000000000
14Yellow100143000000000000
15Yellow4.500002340000000000
16Red4.5000000000000000
17Blue4.500005650000000000
18Green4.5000000000000000
19Blue1.50565001430000000000
20Green1.5000000000000000
21Red1.5000000000000000
22Yellow1.502340000000000000
Sheet3
Cell Formulas
RangeFormula
C11=(SUMPRODUCT(($A$2:$A$8=$A11)*($B$2:$B$8=$B11)*(C$2:C$8)))
C12=SUMPRODUCT(($A$2:$A$8=$A12)*($B$2:$B$8=$B12)*(C$2:C$8))
C13=SUMPRODUCT(($A$2:$A$8=$A13)*($B$2:$B$8=$B13)*(C$2:C$8))
C14=SUMPRODUCT(($A$2:$A$8=$A14)*($B$2:$B$8=$B14)*(C$2:C$8))
C15=SUMPRODUCT(($A$2:$A$8=$A15)*($B$2:$B$8=$B15)*(C$2:C$8))
C16=SUMPRODUCT(($A$2:$A$8=$A16)*($B$2:$B$8=$B16)*(C$2:C$8))
C17=SUMPRODUCT(($A$2:$A$8=$A17)*($B$2:$B$8=$B17)*(C$2:C$8))
C18=SUMPRODUCT(($A$2:$A$8=$A18)*($B$2:$B$8=$B18)*(C$2:C$8))
C19=SUMPRODUCT(($A$2:$A$8=$A19)*($B$2:$B$8=$B19)*(C$2:C$8))
C20=SUMPRODUCT(($A$2:$A$8=$A20)*($B$2:$B$8=$B20)*(C$2:C$8))
C21=SUMPRODUCT(($A$2:$A$8=$A21)*($B$2:$B$8=$B21)*(C$2:C$8))
C22=SUMPRODUCT(($A$2:$A$8=$A22)*($B$2:$B$8=$B22)*(C$2:C$8))
D11=SUMPRODUCT(($A$2:$A$8=$A11)*($B$2:$B$8=$B11)*(D$2:D$8))
D12=SUMPRODUCT(($A$2:$A$8=$A12)*($B$2:$B$8=$B12)*(D$2:D$8))
D13=SUMPRODUCT(($A$2:$A$8=$A13)*($B$2:$B$8=$B13)*(D$2:D$8))
D14=SUMPRODUCT(($A$2:$A$8=$A14)*($B$2:$B$8=$B14)*(D$2:D$8))
D15=SUMPRODUCT(($A$2:$A$8=$A15)*($B$2:$B$8=$B15)*(D$2:D$8))
D16=SUMPRODUCT(($A$2:$A$8=$A16)*($B$2:$B$8=$B16)*(D$2:D$8))
D17=SUMPRODUCT(($A$2:$A$8=$A17)*($B$2:$B$8=$B17)*(D$2:D$8))
D18=SUMPRODUCT(($A$2:$A$8=$A18)*($B$2:$B$8=$B18)*(D$2:D$8))
D19=SUMPRODUCT(($A$2:$A$8=$A19)*($B$2:$B$8=$B19)*(D$2:D$8))
D20=SUMPRODUCT(($A$2:$A$8=$A20)*($B$2:$B$8=$B20)*(D$2:D$8))
D21=SUMPRODUCT(($A$2:$A$8=$A21)*($B$2:$B$8=$B21)*(D$2:D$8))
D22=SUMPRODUCT(($A$2:$A$8=$A22)*($B$2:$B$8=$B22)*(D$2:D$8))
E11=SUMPRODUCT(($A$2:$A$8=$A11)*($B$2:$B$8=$B11)*(E$2:E$8))
E12=SUMPRODUCT(($A$2:$A$8=$A12)*($B$2:$B$8=$B12)*(E$2:E$8))
E13=SUMPRODUCT(($A$2:$A$8=$A13)*($B$2:$B$8=$B13)*(E$2:E$8))
E14=SUMPRODUCT(($A$2:$A$8=$A14)*($B$2:$B$8=$B14)*(E$2:E$8))
E15=SUMPRODUCT(($A$2:$A$8=$A15)*($B$2:$B$8=$B15)*(E$2:E$8))
E16=SUMPRODUCT(($A$2:$A$8=$A16)*($B$2:$B$8=$B16)*(E$2:E$8))
E17=SUMPRODUCT(($A$2:$A$8=$A17)*($B$2:$B$8=$B17)*(E$2:E$8))
E18=SUMPRODUCT(($A$2:$A$8=$A18)*($B$2:$B$8=$B18)*(E$2:E$8))
E19=SUMPRODUCT(($A$2:$A$8=$A19)*($B$2:$B$8=$B19)*(E$2:E$8))
E20=SUMPRODUCT(($A$2:$A$8=$A20)*($B$2:$B$8=$B20)*(E$2:E$8))
E21=SUMPRODUCT(($A$2:$A$8=$A21)*($B$2:$B$8=$B21)*(E$2:E$8))
E22=SUMPRODUCT(($A$2:$A$8=$A22)*($B$2:$B$8=$B22)*(E$2:E$8))
F11=SUMPRODUCT(($A$2:$A$8=$A11)*($B$2:$B$8=$B11)*(F$2:F$8))
F12=SUMPRODUCT(($A$2:$A$8=$A12)*($B$2:$B$8=$B12)*(F$2:F$8))
F13=SUMPRODUCT(($A$2:$A$8=$A13)*($B$2:$B$8=$B13)*(F$2:F$8))
F14=SUMPRODUCT(($A$2:$A$8=$A14)*($B$2:$B$8=$B14)*(F$2:F$8))
F15=SUMPRODUCT(($A$2:$A$8=$A15)*($B$2:$B$8=$B15)*(F$2:F$8))
F16=SUMPRODUCT(($A$2:$A$8=$A16)*($B$2:$B$8=$B16)*(F$2:F$8))
F17=SUMPRODUCT(($A$2:$A$8=$A17)*($B$2:$B$8=$B17)*(F$2:F$8))
F18=SUMPRODUCT(($A$2:$A$8=$A18)*($B$2:$B$8=$B18)*(F$2:F$8))
F19=SUMPRODUCT(($A$2:$A$8=$A19)*($B$2:$B$8=$B19)*(F$2:F$8))
F20=SUMPRODUCT(($A$2:$A$8=$A20)*($B$2:$B$8=$B20)*(F$2:F$8))
F21=SUMPRODUCT(($A$2:$A$8=$A21)*($B$2:$B$8=$B21)*(F$2:F$8))
F22=SUMPRODUCT(($A$2:$A$8=$A22)*($B$2:$B$8=$B22)*(F$2:F$8))
G11=SUMPRODUCT(($A$2:$A$8=$A11)*($B$2:$B$8=$B11)*(G$2:G$8))
G12=SUMPRODUCT(($A$2:$A$8=$A12)*($B$2:$B$8=$B12)*(G$2:G$8))
G13=SUMPRODUCT(($A$2:$A$8=$A13)*($B$2:$B$8=$B13)*(G$2:G$8))
G14=SUMPRODUCT(($A$2:$A$8=$A14)*($B$2:$B$8=$B14)*(G$2:G$8))
G15=SUMPRODUCT(($A$2:$A$8=$A15)*($B$2:$B$8=$B15)*(G$2:G$8))
G16=SUMPRODUCT(($A$2:$A$8=$A16)*($B$2:$B$8=$B16)*(G$2:G$8))
G17=SUMPRODUCT(($A$2:$A$8=$A17)*($B$2:$B$8=$B17)*(G$2:G$8))
G18=SUMPRODUCT(($A$2:$A$8=$A18)*($B$2:$B$8=$B18)*(G$2:G$8))
G19=SUMPRODUCT(($A$2:$A$8=$A19)*($B$2:$B$8=$B19)*(G$2:G$8))
G20=SUMPRODUCT(($A$2:$A$8=$A20)*($B$2:$B$8=$B20)*(G$2:G$8))
G21=SUMPRODUCT(($A$2:$A$8=$A21)*($B$2:$B$8=$B21)*(G$2:G$8))
G22=SUMPRODUCT(($A$2:$A$8=$A22)*($B$2:$B$8=$B22)*(G$2:G$8))
H11=SUMPRODUCT(($A$2:$A$8=$A11)*($B$2:$B$8=$B11)*(H$2:H$8))
H12=SUMPRODUCT(($A$2:$A$8=$A12)*($B$2:$B$8=$B12)*(H$2:H$8))
H13=SUMPRODUCT(($A$2:$A$8=$A13)*($B$2:$B$8=$B13)*(H$2:H$8))
H14=SUMPRODUCT(($A$2:$A$8=$A14)*($B$2:$B$8=$B14)*(H$2:H$8))
H15=SUMPRODUCT(($A$2:$A$8=$A15)*($B$2:$B$8=$B15)*(H$2:H$8))
H16=SUMPRODUCT(($A$2:$A$8=$A16)*($B$2:$B$8=$B16)*(H$2:H$8))
H17=SUMPRODUCT(($A$2:$A$8=$A17)*($B$2:$B$8=$B17)*(H$2:H$8))
H18=SUMPRODUCT(($A$2:$A$8=$A18)*($B$2:$B$8=$B18)*(H$2:H$8))
H19=SUMPRODUCT(($A$2:$A$8=$A19)*($B$2:$B$8=$B19)*(H$2:H$8))
H20=SUMPRODUCT(($A$2:$A$8=$A20)*($B$2:$B$8=$B20)*(H$2:H$8))
H21=SUMPRODUCT(($A$2:$A$8=$A21)*($B$2:$B$8=$B21)*(H$2:H$8))
H22=SUMPRODUCT(($A$2:$A$8=$A22)*($B$2:$B$8=$B22)*(H$2:H$8))
I11=SUMPRODUCT(($A$2:$A$8=$A11)*($B$2:$B$8=$B11)*(I$2:I$8))
I12=SUMPRODUCT(($A$2:$A$8=$A12)*($B$2:$B$8=$B12)*(I$2:I$8))
I13=SUMPRODUCT(($A$2:$A$8=$A13)*($B$2:$B$8=$B13)*(I$2:I$8))
I14=SUMPRODUCT(($A$2:$A$8=$A14)*($B$2:$B$8=$B14)*(I$2:I$8))
I15=SUMPRODUCT(($A$2:$A$8=$A15)*($B$2:$B$8=$B15)*(I$2:I$8))
I16=SUMPRODUCT(($A$2:$A$8=$A16)*($B$2:$B$8=$B16)*(I$2:I$8))
I17=SUMPRODUCT(($A$2:$A$8=$A17)*($B$2:$B$8=$B17)*(I$2:I$8))
I18=SUMPRODUCT(($A$2:$A$8=$A18)*($B$2:$B$8=$B18)*(I$2:I$8))
I19=SUMPRODUCT(($A$2:$A$8=$A19)*($B$2:$B$8=$B19)*(I$2:I$8))
I20=SUMPRODUCT(($A$2:$A$8=$A20)*($B$2:$B$8=$B20)*(I$2:I$8))
I21=SUMPRODUCT(($A$2:$A$8=$A21)*($B$2:$B$8=$B21)*(I$2:I$8))
I22=SUMPRODUCT(($A$2:$A$8=$A22)*($B$2:$B$8=$B22)*(I$2:I$8))
J11=SUMPRODUCT(($A$2:$A$8=$A11)*($B$2:$B$8=$B11)*(J$2:J$8))
J12=SUMPRODUCT(($A$2:$A$8=$A12)*($B$2:$B$8=$B12)*(J$2:J$8))
J13=SUMPRODUCT(($A$2:$A$8=$A13)*($B$2:$B$8=$B13)*(J$2:J$8))
J14=SUMPRODUCT(($A$2:$A$8=$A14)*($B$2:$B$8=$B14)*(J$2:J$8))
J15=SUMPRODUCT(($A$2:$A$8=$A15)*($B$2:$B$8=$B15)*(J$2:J$8))
J16=SUMPRODUCT(($A$2:$A$8=$A16)*($B$2:$B$8=$B16)*(J$2:J$8))
J17=SUMPRODUCT(($A$2:$A$8=$A17)*($B$2:$B$8=$B17)*(J$2:J$8))
J18=SUMPRODUCT(($A$2:$A$8=$A18)*($B$2:$B$8=$B18)*(J$2:J$8))
J19=SUMPRODUCT(($A$2:$A$8=$A19)*($B$2:$B$8=$B19)*(J$2:J$8))
J20=SUMPRODUCT(($A$2:$A$8=$A20)*($B$2:$B$8=$B20)*(J$2:J$8))
J21=SUMPRODUCT(($A$2:$A$8=$A21)*($B$2:$B$8=$B21)*(J$2:J$8))
J22=SUMPRODUCT(($A$2:$A$8=$A22)*($B$2:$B$8=$B22)*(J$2:J$8))
K11=SUMPRODUCT(($A$2:$A$8=$A11)*($B$2:$B$8=$B11)*(K$2:K$8))
K12=SUMPRODUCT(($A$2:$A$8=$A12)*($B$2:$B$8=$B12)*(K$2:K$8))
K13=SUMPRODUCT(($A$2:$A$8=$A13)*($B$2:$B$8=$B13)*(K$2:K$8))
K14=SUMPRODUCT(($A$2:$A$8=$A14)*($B$2:$B$8=$B14)*(K$2:K$8))
K15=SUMPRODUCT(($A$2:$A$8=$A15)*($B$2:$B$8=$B15)*(K$2:K$8))
K16=SUMPRODUCT(($A$2:$A$8=$A16)*($B$2:$B$8=$B16)*(K$2:K$8))
K17=SUMPRODUCT(($A$2:$A$8=$A17)*($B$2:$B$8=$B17)*(K$2:K$8))
K18=SUMPRODUCT(($A$2:$A$8=$A18)*($B$2:$B$8=$B18)*(K$2:K$8))
K19=SUMPRODUCT(($A$2:$A$8=$A19)*($B$2:$B$8=$B19)*(K$2:K$8))
K20=SUMPRODUCT(($A$2:$A$8=$A20)*($B$2:$B$8=$B20)*(K$2:K$8))
K21=SUMPRODUCT(($A$2:$A$8=$A21)*($B$2:$B$8=$B21)*(K$2:K$8))
K22=SUMPRODUCT(($A$2:$A$8=$A22)*($B$2:$B$8=$B22)*(K$2:K$8))
L11=SUMPRODUCT(($A$2:$A$8=$A11)*($B$2:$B$8=$B11)*(L$2:L$8))
L12=SUMPRODUCT(($A$2:$A$8=$A12)*($B$2:$B$8=$B12)*(L$2:L$8))
L13=SUMPRODUCT(($A$2:$A$8=$A13)*($B$2:$B$8=$B13)*(L$2:L$8))
L14=SUMPRODUCT(($A$2:$A$8=$A14)*($B$2:$B$8=$B14)*(L$2:L$8))
L15=SUMPRODUCT(($A$2:$A$8=$A15)*($B$2:$B$8=$B15)*(L$2:L$8))
L16=SUMPRODUCT(($A$2:$A$8=$A16)*($B$2:$B$8=$B16)*(L$2:L$8))
L17=SUMPRODUCT(($A$2:$A$8=$A17)*($B$2:$B$8=$B17)*(L$2:L$8))
L18=SUMPRODUCT(($A$2:$A$8=$A18)*($B$2:$B$8=$B18)*(L$2:L$8))
L19=SUMPRODUCT(($A$2:$A$8=$A19)*($B$2:$B$8=$B19)*(L$2:L$8))
L20=SUMPRODUCT(($A$2:$A$8=$A20)*($B$2:$B$8=$B20)*(L$2:L$8))
L21=SUMPRODUCT(($A$2:$A$8=$A21)*($B$2:$B$8=$B21)*(L$2:L$8))
L22=SUMPRODUCT(($A$2:$A$8=$A22)*($B$2:$B$8=$B22)*(L$2:L$8))
M11=SUMPRODUCT(($A$2:$A$8=$A11)*($B$2:$B$8=$B11)*(M$2:M$8))
M12=SUMPRODUCT(($A$2:$A$8=$A12)*($B$2:$B$8=$B12)*(M$2:M$8))
M13=SUMPRODUCT(($A$2:$A$8=$A13)*($B$2:$B$8=$B13)*(M$2:M$8))
M14=SUMPRODUCT(($A$2:$A$8=$A14)*($B$2:$B$8=$B14)*(M$2:M$8))
M15=SUMPRODUCT(($A$2:$A$8=$A15)*($B$2:$B$8=$B15)*(M$2:M$8))
M16=SUMPRODUCT(($A$2:$A$8=$A16)*($B$2:$B$8=$B16)*(M$2:M$8))
M17=SUMPRODUCT(($A$2:$A$8=$A17)*($B$2:$B$8=$B17)*(M$2:M$8))
M18=SUMPRODUCT(($A$2:$A$8=$A18)*($B$2:$B$8=$B18)*(M$2:M$8))
M19=SUMPRODUCT(($A$2:$A$8=$A19)*($B$2:$B$8=$B19)*(M$2:M$8))
M20=SUMPRODUCT(($A$2:$A$8=$A20)*($B$2:$B$8=$B20)*(M$2:M$8))
M21=SUMPRODUCT(($A$2:$A$8=$A21)*($B$2:$B$8=$B21)*(M$2:M$8))
M22=SUMPRODUCT(($A$2:$A$8=$A22)*($B$2:$B$8=$B22)*(M$2:M$8))
N11=SUMPRODUCT(($A$2:$A$8=$A11)*($B$2:$B$8=$B11)*(N$2:N$8))
N12=SUMPRODUCT(($A$2:$A$8=$A12)*($B$2:$B$8=$B12)*(N$2:N$8))
N13=SUMPRODUCT(($A$2:$A$8=$A13)*($B$2:$B$8=$B13)*(N$2:N$8))
N14=SUMPRODUCT(($A$2:$A$8=$A14)*($B$2:$B$8=$B14)*(N$2:N$8))
N15=SUMPRODUCT(($A$2:$A$8=$A15)*($B$2:$B$8=$B15)*(N$2:N$8))
N16=SUMPRODUCT(($A$2:$A$8=$A16)*($B$2:$B$8=$B16)*(N$2:N$8))
N17=SUMPRODUCT(($A$2:$A$8=$A17)*($B$2:$B$8=$B17)*(N$2:N$8))
N18=SUMPRODUCT(($A$2:$A$8=$A18)*($B$2:$B$8=$B18)*(N$2:N$8))
N19=SUMPRODUCT(($A$2:$A$8=$A19)*($B$2:$B$8=$B19)*(N$2:N$8))
N20=SUMPRODUCT(($A$2:$A$8=$A20)*($B$2:$B$8=$B20)*(N$2:N$8))
N21=SUMPRODUCT(($A$2:$A$8=$A21)*($B$2:$B$8=$B21)*(N$2:N$8))
N22=SUMPRODUCT(($A$2:$A$8=$A22)*($B$2:$B$8=$B22)*(N$2:N$8))
O11=SUMPRODUCT(($A$2:$A$8=$A11)*($B$2:$B$8=$B11)*(O$2:O$8))
O12=SUMPRODUCT(($A$2:$A$8=$A12)*($B$2:$B$8=$B12)*(O$2:O$8))
O13=SUMPRODUCT(($A$2:$A$8=$A13)*($B$2:$B$8=$B13)*(O$2:O$8))
O14=SUMPRODUCT(($A$2:$A$8=$A14)*($B$2:$B$8=$B14)*(O$2:O$8))
O15=SUMPRODUCT(($A$2:$A$8=$A15)*($B$2:$B$8=$B15)*(O$2:O$8))
O16=SUMPRODUCT(($A$2:$A$8=$A16)*($B$2:$B$8=$B16)*(O$2:O$8))
O17=SUMPRODUCT(($A$2:$A$8=$A17)*($B$2:$B$8=$B17)*(O$2:O$8))
O18=SUMPRODUCT(($A$2:$A$8=$A18)*($B$2:$B$8=$B18)*(O$2:O$8))
O19=SUMPRODUCT(($A$2:$A$8=$A19)*($B$2:$B$8=$B19)*(O$2:O$8))
O20=SUMPRODUCT(($A$2:$A$8=$A20)*($B$2:$B$8=$B20)*(O$2:O$8))
O21=SUMPRODUCT(($A$2:$A$8=$A21)*($B$2:$B$8=$B21)*(O$2:O$8))
O22=SUMPRODUCT(($A$2:$A$8=$A22)*($B$2:$B$8=$B22)*(O$2:O$8))
P11=SUMPRODUCT(($A$2:$A$8=$A11)*($B$2:$B$8=$B11)*(P$2:P$8))
P12=SUMPRODUCT(($A$2:$A$8=$A12)*($B$2:$B$8=$B12)*(P$2:P$8))
P13=SUMPRODUCT(($A$2:$A$8=$A13)*($B$2:$B$8=$B13)*(P$2:P$8))
P14=SUMPRODUCT(($A$2:$A$8=$A14)*($B$2:$B$8=$B14)*(P$2:P$8))
P15=SUMPRODUCT(($A$2:$A$8=$A15)*($B$2:$B$8=$B15)*(P$2:P$8))
P16=SUMPRODUCT(($A$2:$A$8=$A16)*($B$2:$B$8=$B16)*(P$2:P$8))
P17=SUMPRODUCT(($A$2:$A$8=$A17)*($B$2:$B$8=$B17)*(P$2:P$8))
P18=SUMPRODUCT(($A$2:$A$8=$A18)*($B$2:$B$8=$B18)*(P$2:P$8))
P19=SUMPRODUCT(($A$2:$A$8=$A19)*($B$2:$B$8=$B19)*(P$2:P$8))
P20=SUMPRODUCT(($A$2:$A$8=$A20)*($B$2:$B$8=$B20)*(P$2:P$8))
P21=SUMPRODUCT(($A$2:$A$8=$A21)*($B$2:$B$8=$B21)*(P$2:P$8))
P22=SUMPRODUCT(($A$2:$A$8=$A22)*($B$2:$B$8=$B22)*(P$2:P$8))
Q11=SUMPRODUCT(($A$2:$A$8=$A11)*($B$2:$B$8=$B11)*(Q$2:Q$8))
Q12=SUMPRODUCT(($A$2:$A$8=$A12)*($B$2:$B$8=$B12)*(Q$2:Q$8))
Q13=SUMPRODUCT(($A$2:$A$8=$A13)*($B$2:$B$8=$B13)*(Q$2:Q$8))
Q14=SUMPRODUCT(($A$2:$A$8=$A14)*($B$2:$B$8=$B14)*(Q$2:Q$8))
Q15=SUMPRODUCT(($A$2:$A$8=$A15)*($B$2:$B$8=$B15)*(Q$2:Q$8))
Q16=SUMPRODUCT(($A$2:$A$8=$A16)*($B$2:$B$8=$B16)*(Q$2:Q$8))
Q17=SUMPRODUCT(($A$2:$A$8=$A17)*($B$2:$B$8=$B17)*(Q$2:Q$8))
Q18=SUMPRODUCT(($A$2:$A$8=$A18)*($B$2:$B$8=$B18)*(Q$2:Q$8))
Q19=SUMPRODUCT(($A$2:$A$8=$A19)*($B$2:$B$8=$B19)*(Q$2:Q$8))
Q20=SUMPRODUCT(($A$2:$A$8=$A20)*($B$2:$B$8=$B20)*(Q$2:Q$8))
Q21=SUMPRODUCT(($A$2:$A$8=$A21)*($B$2:$B$8=$B21)*(Q$2:Q$8))
Q22=SUMPRODUCT(($A$2:$A$8=$A22)*($B$2:$B$8=$B22)*(Q$2:Q$8))
 
  • Like
Reactions: yky
Upvote 0
Re: Help with Index & Match... not working at all!

Thank you yky :) this worked really well !!!!

I'm running into a small problem. Since I update the file below - and it's a pivot table now my formulas don't work :( I'll have to find out why that is happening.

Thank you!!!!
Geger
 
Upvote 0
Re: Help with Index & Match... not working at all!

Thank you for sending me your solution - I have noted this as a second option for me :)

thank you kindly
geger
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
Sumif/Sumproduct with non-continuous row
Replies
2
Views
87
Goddess
G
Nlhicks
  • Question Question
Conditional Format to turn Red if 5 yrs out, Yellow if 4 yrs out and green otherwise
2
Replies
13
Views
347
etaf
etaf
W
  • Solved
Filter/Matching HELP
Replies
5
Views
457
Alex Blakenburg
A
M
  • Question Question
Formula help
Replies
2
Views
104
Peter_SSs
Peter_SSs
M
  • Question Question
automate the sheet
Replies
0
Views
348
mwvirk
M

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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