index match multiple criteria in different sheet

leyan

New Member
Joined
Nov 18, 2013
Messages
9
HI All,

I have below index match function but return with #REF! error message, i have evaulate Formula and it seems locating to the correct cell, but why it shows #REF! message?

Liebherr Demand Forecast 08.04.22.xlsx
ABCDEFGHIJKLMNOP
3ProductProduct DescProduct DepaYearJanFebMarAprMayJunJulAugSepOctNovDec
4ECBN5066ECBN5066-23 136 75cm INTEGBIOFRESH FRID FREEZ RH HINGE3PRO20200001632602178103214
50%0%0%20%0%0%0%0%20%0%0%0%
6ECBN506620215351822111920107111520
70%0%0%20%0%0%0%0%20%0%0%0%
8ECBN50662022251730100000000
90%0%0%20%0%0%0%0%20%0%0%0%
10ECBN50662023000000000000
110%0%0%20%0%0%0%0%20%0%0%0%
Dashboard Monthly
Cell Formulas
RangeFormula
A4A4='Product list'!A2
B4B4=XLOOKUP(A4,'Product list'!A:A,'Product list'!B:B)
C4C4=VLOOKUP(A4,'Product list'!A:C,3,FALSE)
E4:P4E4=SUMIFS('History Month'!B:B,'History Month'!$A:$A,'Dashboard Monthly'!$A4)
E5:P5E5=IFERROR(INDEX('Promo Grid'!C:C,MATCH('Dashboard Monthly'!$A4,'Promo Grid'!$A:$A,0)),"")
E6:P6E6=SUMIFS('History Month'!N:N,'History Month'!$A:$A,'Dashboard Monthly'!$A4)
E7:P7E7=IFERROR(INDEX('Promo Grid'!O:O,MATCH('Dashboard Monthly'!$A4,'Promo Grid'!$A:$A,0)),"")
E8:P8E8=SUMIFS('History Month'!Z:Z,'History Month'!$A:$A,'Dashboard Monthly'!$A4)
E9:P9E9=IFERROR(INDEX('Promo Grid'!AA:AA,MATCH('Dashboard Monthly'!$A4,'Promo Grid'!$A:$A,0)),"")
E10:P10E10=SUMIFS('History Month'!AL:AL,'History Month'!$A:$A,'Dashboard Monthly'!$A4)
E11:P11E11=IFERROR(INDEX('Promo Grid'!AM:AM,MATCH('Dashboard Monthly'!$A4,'Promo Grid'!$A:$A,0)),"")
Named Ranges
NameRefers ToCells
'Product list'!_FilterDatabase='Product list'!$A$1:$C$62B4:C4
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E9:P9Other TypeDataBarNO
E7:P7Other TypeDataBarNO
E11:P11Other TypeDataBarNO
E5:P5Other TypeDataBarNO


Liebherr Demand Forecast 08.04.22.xlsx
ABCDEF
120222022202220222022
2ItemAprMayJunJulAug
3ECBN5066#REF!#REF!#REF!#REF!#REF!
Forecast
Cell Formulas
RangeFormula
A3A3='Product list'!A2
B3:F3B3=INDEX('Dashboard Monthly'!$E$4:$P$1568,MATCH(Forecast!$A3,'Dashboard Monthly'!$A:$A,0),MATCH(Forecast!$B$2,'Dashboard Monthly'!$E$3:$P$3,0),MATCH(Forecast!$B$1,'Dashboard Monthly'!$D:$D,0))
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
6,188
Office Version
  1. 365
Platform
  1. MacOS
how about


=INDEX('Dashboard Monthly'!$E$4:$P$1568,MATCH(Forecast!$A3&B$1,'Dashboard Monthly'!$A:$A&'Dashboard Monthly'!$B:$B,0),MATCH(Forecast!B$2,'Dashboard Monthly'!$E$3:$P$3,0)))

I will try on your sheets , - it works on the same sheet

as you can see here - BUT i will setup , for the multiple sheets and reply either here if in 10mins or a new post

Cell Formulas
RangeFormula
J2J2='Product list'!J2
K2K2=XLOOKUP(J4,'Product list'!J:J,'Product list'!K:K)
L2L2=VLOOKUP(J4,'Product list'!J:L,3,FALSE)
N2:Y2N2=SUMIFS('History Month'!K:K,'History Month'!$A:$A,'Dashboard Monthly'!$A4)
N3:Y3N3=IFERROR(INDEX('Promo Grid'!L:L,MATCH('Dashboard Monthly'!$A4,'Promo Grid'!$A:$A,0)),"")
N4:Y4N4=SUMIFS('History Month'!W:W,'History Month'!$A:$A,'Dashboard Monthly'!$A4)
N5:Y5N5=IFERROR(INDEX('Promo Grid'!X:X,MATCH('Dashboard Monthly'!$A4,'Promo Grid'!$A:$A,0)),"")
R6:Y6,N6:P6R6=SUMIFS('History Month'!AM:AM,'History Month'!$A:$A,'Dashboard Monthly'!$A4)
B3:F3B3=INDEX('Dashboard Monthly'!$E$4:$P$1568,MATCH(Forecast!$A3,'Dashboard Monthly'!$A:$A,0),MATCH(Forecast!$B$2,'Dashboard Monthly'!$E$3:$P$3,0),MATCH(Forecast!$B$1,'Dashboard Monthly'!$D:$D,0))
B7B7=INDEX(N2:Y8,MATCH($A3&B1,J2:J8&M2:M8,0),MATCH(B$2,N1:Y1,0))
N7:Y7N7=IFERROR(INDEX('Promo Grid'!AJ:AJ,MATCH('Dashboard Monthly'!$A4,'Promo Grid'!$A:$A,0)),"")
N8:Y8N8=SUMIFS('History Month'!AU:AU,'History Month'!$A:$A,'Dashboard Monthly'!$A4)
N9:Y9N9=IFERROR(INDEX('Promo Grid'!AV:AV,MATCH('Dashboard Monthly'!$A4,'Promo Grid'!$A:$A,0)),"")
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
6,188
Office Version
  1. 365
Platform
  1. MacOS
ok, i have added the sheets and modified the formula

Note i added some text for a month a year, and changed the year in forecast to check it works ok

Book2
ABCDEF
120222021202320202021
2ItemAprMayJunJulAug
3ECBN5066Apr-22May-21Jun-23Jul-20Aug-21
Forecast
Cell Formulas
RangeFormula
B3:F3B3=INDEX('Dashboard Monthly'!$E$2:$P$8,MATCH($A3&B$1,'Dashboard Monthly'!$A$2:$A$8&'Dashboard Monthly'!$D$2:$D$8,0),MATCH(B$2,'Dashboard Monthly'!$E$1:$P$1,0))



Cell Formulas
RangeFormula
B2B2=XLOOKUP(A4,'Product list'!A:A,'Product list'!B:B)
C2C2=VLOOKUP(A4,'Product list'!A:C,3,FALSE)
E2:J2,L2:P2E2=SUMIFS('History Month'!B:B,'History Month'!$A:$A,'Dashboard Monthly'!$A4)
E3:P3M3=IFERROR(INDEX('Promo Grid'!K:K,MATCH('Dashboard Monthly'!$A4,'Promo Grid'!$A:$A,0)),"")
M4:P4,J4:K4,E4:H4M4=SUMIFS('History Month'!V:V,'History Month'!$A:$A,'Dashboard Monthly'!$A4)
E5:P5E5=IFERROR(INDEX('Promo Grid'!O:O,MATCH('Dashboard Monthly'!$A4,'Promo Grid'!$A:$A,0)),"")
E6:G6,I6:P6I6=SUMIFS('History Month'!AD:AD,'History Month'!$A:$A,'Dashboard Monthly'!$A4)
E7:P7K7=IFERROR(INDEX('Promo Grid'!AG:AG,MATCH('Dashboard Monthly'!$A4,'Promo Grid'!$A:$A,0)),"")
K8:P8,E8:I8K8=SUMIFS('History Month'!AR:AR,'History Month'!$A:$A,'Dashboard Monthly'!$A4)
E9:P9E9=IFERROR(INDEX('Promo Grid'!AM:AM,MATCH('Dashboard Monthly'!$A4,'Promo Grid'!$A:$A,0)),"")
 

leyan

New Member
Joined
Nov 18, 2013
Messages
9
Thank you and it works. so for index function, the column must be the last criteria, and all other variables/arrays need to be put together.
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
6,188
Office Version
  1. 365
Platform
  1. MacOS
and all other variables/arrays need to be put together.
depends on what you are doing specifically
but
index( array, row, column)


In this case we have the data for the row in 2 columns A & B we need to find out which row both those 2 variables are in

in this case we have concatenate
the 2 row values
for example
ECBN50662022
now we are looking up in a row that value
by concatenating the range
A2:A100&B2:B100

so that will return the row

hope that helps
 

Forum statistics

Threads
1,176,640
Messages
5,904,198
Members
435,076
Latest member
71672618

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