index match multiple criteria in different sheet

L

leyan

New Member
Joined
Nov 18, 2013
Messages
13
Office Version
  1. 365
Platform
  1. Windows
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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
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

Book2
ABCDEFGHIJKLMNOPQRSTUVWXY
120222022202220222022ProductProduct DescProduct DepaYearJanFebMarAprMayJunJulAugSepOctNovDec
2ItemAprMayJunJulAug#REF!#N/A#N/A2020#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!
3ECBN5066#REF!#REF!#REF!#REF!#REF!            
4ECBN50662021#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!
5            
6ECBN50662022#VALUE!#VALUE!#VALUE!22#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!
722            
8ECBN50662023#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!
9            
10
Forecast
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)),"")
 
Upvote 0
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))



Book2
ABCDEFGHIJKLMNOP
1ProductProduct DescProduct DepaYearJanFebMarAprMayJunJulAugSepOctNovDec
2ECBN5066#N/A#N/A2020#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!Jul-20#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!
3            
4ECBN50662021#VALUE!#VALUE!#VALUE!#VALUE!May-21#VALUE!#VALUE!Aug-21#VALUE!#VALUE!#VALUE!#VALUE!
5            
6ECBN50662022#VALUE!#VALUE!#VALUE!Apr-22#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!
7            
8ECBN50662023#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!Jun-23#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!
9            
Dashboard Monthly
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)),"")
 
Upvote 0
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.
 
Upvote 0
and all other variables/arrays need to be put together.
Click to expand...
depends on what you are doing specifically
but
index( array, row, column)

INDEX function - Microsoft Support

How to use the INDEX function in Excel to return a value or reference to a value within a table or range.
support.microsoft.com support.microsoft.com

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
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Using index and match from another source excel file
2
Replies
10
Views
666
Peter_SSs
Peter_SSs
P
  • Question Question
Populating, aggregating and sorting multiple matching values in Excel
Replies
0
Views
198
Pbobas
P
W
  • Solved
Filter/Matching HELP
Replies
5
Views
451
Alex Blakenburg
A
S
  • Solved
Help with a unit convert table with formulas and multiple index/match criteria
2
Replies
13
Views
626
Dave Patton
D
R
  • Solved
Find multiple matches with wildcard search
Replies
6
Views
532
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,893
Messages
6,175,242
Members
452,623
Latest member
russelllowellpercy

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