INDEX MATCH with Multiple Criteria and Multiple Sheets

[Lalalauren191]

So maybe I need to use a different formula entirely, but here is my data first and then my question.

• Location is cell B2
• Month is cell B3
• Sheets that the data is being pulled from are:
  • GSP D18
  • GSP D35
  • GSP D110
  • GSP D121
• Index range on each sheet is B5:BQ17
• Match Range on each sheet for location names is A5:A17
• Match Range on each sheet for month is B3:BQ3
• My full current formula is: =INDEX('GSP D18'!$B$5:$BQ$17,MATCH($B$2,'GSP D18'!$A$5:$A$17,0),MATCH($B$3,'GSP D18'!$B$3:$BQ$3,0))

My issue is that this formula works to pull data from GSP D18, but I cannot figure out a way to get it to look at multiple sheets. Ultimately I need a formula that will look for a location number on each sheet and return data based on both the month and location since it is a trend file. It is not possible to add all the information to 1 sheet, since each sheet contains so much additional information that is not in the range provided.

Hopefully this was enough information.

 

[Saba Sabaratnam]

Hi,

Based on my understanding of your requirement and assumption that the same location will not be repeated in more than one worksheet, I have developed a solution to your Excel problem.

Enter the Index range, Location Range and Date range as shown below and then enter the following formula in Cell B5

=IF(ISNA(INDEX(INDIRECT(D2),MATCH(Sheet3!$B$2,INDIRECT(E2),0),MATCH($B$3,INDIRECT(F2),0)))=TRUE,IF(ISNA(INDEX(INDIRECT(D3),MATCH(Sheet3!$B$2,INDIRECT(E3),0),MATCH($B$3,INDIRECT(F3),0)))=TRUE,IF(ISNA(INDEX(INDIRECT(D4),MATCH(Sheet3!$B$2,INDIRECT(E4),0),MATCH($B$3,INDIRECT(F4),0)))=TRUE, INDEX(INDIRECT(D5),MATCH(Sheet3!$B$2,INDIRECT(E5),0),MATCH($B$3,INDIRECT(F5),0)),INDEX(INDIRECT(D4),MATCH(Sheet3!$B$2,INDIRECT(E4),0),MATCH($B$3,INDIRECT(F4),0))),INDEX(INDIRECT(D3),MATCH(Sheet3!$B$2,INDIRECT(E3),0),MATCH($B$3,INDIRECT(F3),0))),INDEX(INDIRECT(D2),MATCH(Sheet3!$B$2,INDIRECT(E2),0),MATCH($B$3,INDIRECT(F2),0)))

The above formula will the location in the second worksheet if I could not find the location in the first worksheet and so on.

Kind regards

Saba

 

[StephenCrump]

Welcome to the Forum!

Am I correct in guessing that GSP is some sort of location grouping, so that you could generate a table that would look something like this:

Book3
	A	B
1	Location	Group/Sheet name
2	A	GSP D18
3	B	GSP D121
4	C	GSP D18
5	D	GSP D18
6	E	GSP D35
7	F	GSP D110
8	G	GSP D18
9	H	GSP D110
10	I	GSP D110
11	J	GSP D35
Sheet1

If so, we can replace your current hard coded sheet name GSP D18, with a simple INDIRECT() function.

 

[StephenCrump]

Here's what I had in mind.

LocationTable: =MASTER!$D$2:$E$11
SheetList: =MASTER!$E$14:$E$17

I have used a helper cell in B4, rather than combining into one unwieldy formula.

If you don't have LocationTable, you could bypass and go direct to:
B4: ="'"&INDEX(SheetList,MATCH(1,COUNTIF(INDIRECT("'"&SheetList&"'!A$5:A$17"),B2)^0,))&"'!"

	A	B	C	D	E
1				Location	Group/Sheet name
2	Location	H		A	GSP D18
3	Month	Feb 2020		B	GSP D121
4	Sheet	'GSP D110'!		C	GSP D18
5	Value	7777		D	GSP D18
6				E	GSP D35
7				F	GSP D110
8				G	GSP D18
9				H	GSP D110
10				I	GSP D110
11				J	GSP D35
12
13					SheetList
14					GSP D18
15					GSP D35
16					GSP D110
17					GSP D121
MASTER

Cell Formulas
Range	Formula
B4	B4	="'"&VLOOKUP(B2,LocationTable,2,)&"'!"
B5	B5	=INDEX(INDIRECT(B4&"$B$5:$BQ$17"),MATCH($B$2,INDIRECT(B4&"A$5:$A$17"),),MATCH($B$3,INDIRECT(B4&"B$3:$BQ$3"),))
E2:E11	E2	=INDEX(SheetList,MATCH(1,COUNTIF(INDIRECT("'"&SheetList&"'!A$5:A$17"),D2)^0,))

	A	B	C	D	E	F
1
2
3		Jan 2020	Feb 2020	Mar 2020	Apr 2020	May 2020
4
5	x
6	x
7	x
8	x
9	H		7777	8888
10	F
11	I
GSP D110

 

[Lalalauren191]

Welcome to the Forum!

Am I correct in guessing that GSP is some sort of location grouping, so that you could generate a table that would look something like this:

Book3
	A	B
1	Location	Group/Sheet name
2	A	GSP D18
3	B	GSP D121
4	C	GSP D18
5	D	GSP D18
6	E	GSP D35
7	F	GSP D110
8	G	GSP D18
9	H	GSP D110
10	I	GSP D110
11	J	GSP D35
Sheet1

If so, we can replace your current hard coded sheet name GSP D18, with a simple INDIRECT() function.

So locations would be like store 500 to 515 on the first sheet, store 516-530 on the second, and so on. So the formula would need to find the location and the month and then find the GSP percentage and bring that number back.

 

[Lalalauren191]

Here's what I had in mind.

LocationTable: =MASTER!$D$2:$E$11
SheetList: =MASTER!$E$14:$E$17

I have used a helper cell in B4, rather than combining into one unwieldy formula.

If you don't have LocationTable, you could bypass and go direct to:
B4: ="'"&INDEX(SheetList,MATCH(1,COUNTIF(INDIRECT("'"&SheetList&"'!A$5:A$17"),B2)^0,))&"'!"

	A	B	C	D	E
1				Location	Group/Sheet name
2	Location	H		A	GSP D18
3	Month	Feb 2020		B	GSP D121
4	Sheet	'GSP D110'!		C	GSP D18
5	Value	7777		D	GSP D18
6				E	GSP D35
7				F	GSP D110
8				G	GSP D18
9				H	GSP D110
10				I	GSP D110
11				J	GSP D35
12
13					SheetList
14					GSP D18
15					GSP D35
16					GSP D110
17					GSP D121
MASTER

Cell Formulas
Range	Formula
B4	B4	="'"&VLOOKUP(B2,LocationTable,2,)&"'!"
B5	B5	=INDEX(INDIRECT(B4&"$B$5:$BQ$17"),MATCH($B$2,INDIRECT(B4&"A$5:$A$17"),),MATCH($B$3,INDIRECT(B4&"B$3:$BQ$3"),))
E2:E11	E2	=INDEX(SheetList,MATCH(1,COUNTIF(INDIRECT("'"&SheetList&"'!A$5:A$17"),D2)^0,))

	A	B	C	D	E	F
1
2
3		Jan 2020	Feb 2020	Mar 2020	Apr 2020	May 2020
4
5	x
6	x
7	x
8	x
9	H		7777	8888
10	F
11	I
GSP D110

I am going to try this out today and I will let you know if it works. Thank you so much!

 

[Lalalauren191]

Here's what I had in mind.

LocationTable: =MASTER!$D$2:$E$11
SheetList: =MASTER!$E$14:$E$17

I have used a helper cell in B4, rather than combining into one unwieldy formula.

If you don't have LocationTable, you could bypass and go direct to:
B4: ="'"&INDEX(SheetList,MATCH(1,COUNTIF(INDIRECT("'"&SheetList&"'!A$5:A$17"),B2)^0,))&"'!"

	A	B	C	D	E
1				Location	Group/Sheet name
2	Location	H		A	GSP D18
3	Month	Feb 2020		B	GSP D121
4	Sheet	'GSP D110'!		C	GSP D18
5	Value	7777		D	GSP D18
6				E	GSP D35
7				F	GSP D110
8				G	GSP D18
9				H	GSP D110
10				I	GSP D110
11				J	GSP D35
12
13					SheetList
14					GSP D18
15					GSP D35
16					GSP D110
17					GSP D121
MASTER

Cell Formulas
Range	Formula
B4	B4	="'"&VLOOKUP(B2,LocationTable,2,)&"'!"
B5	B5	=INDEX(INDIRECT(B4&"$B$5:$BQ$17"),MATCH($B$2,INDIRECT(B4&"A$5:$A$17"),),MATCH($B$3,INDIRECT(B4&"B$3:$BQ$3"),))
E2:E11	E2	=INDEX(SheetList,MATCH(1,COUNTIF(INDIRECT("'"&SheetList&"'!A$5:A$17"),D2)^0,))

	A	B	C	D	E	F
1
2
3		Jan 2020	Feb 2020	Mar 2020	Apr 2020	May 2020
4
5	x
6	x
7	x
8	x
9	H		7777	8888
10	F
11	I
GSP D110

Hi!

This works BEAUTIFULLY and I learned something new! Thank you so much. I do have 1 question though since this could be an issue down the road, what do I do if the ranges are different? So for example the range on 3 of the sheets is:

• Index range on each sheet is B5:BQ17
• Match Range on each sheet for location names is A5:A17
• Match Range on each sheet for month is B3:BQ3

and the range on one sheet is:

• Index range on each sheet is B5:BQ15
• Match Range on each sheet for location names is A5:A15
• Match Range on each sheet for month is B3:BQ3

The only reason I can see having this issue is less stores in one district compared to another. How would the formula change to accommodate that?

Again thank you so much! You are brilliant!

 

[Lalalauren191]

Hi,

Based on my understanding of your requirement and assumption that the same location will not be repeated in more than one worksheet, I have developed a solution to your Excel problem.

Enter the Index range, Location Range and Date range as shown below and then enter the following formula in Cell B5

=IF(ISNA(INDEX(INDIRECT(D2),MATCH(Sheet3!$B$2,INDIRECT(E2),0),MATCH($B$3,INDIRECT(F2),0)))=TRUE,IF(ISNA(INDEX(INDIRECT(D3),MATCH(Sheet3!$B$2,INDIRECT(E3),0),MATCH($B$3,INDIRECT(F3),0)))=TRUE,IF(ISNA(INDEX(INDIRECT(D4),MATCH(Sheet3!$B$2,INDIRECT(E4),0),MATCH($B$3,INDIRECT(F4),0)))=TRUE, INDEX(INDIRECT(D5),MATCH(Sheet3!$B$2,INDIRECT(E5),0),MATCH($B$3,INDIRECT(F5),0)),INDEX(INDIRECT(D4),MATCH(Sheet3!$B$2,INDIRECT(E4),0),MATCH($B$3,INDIRECT(F4),0))),INDEX(INDIRECT(D3),MATCH(Sheet3!$B$2,INDIRECT(E3),0),MATCH($B$3,INDIRECT(F3),0))),INDEX(INDIRECT(D2),MATCH(Sheet3!$B$2,INDIRECT(E2),0),MATCH($B$3,INDIRECT(F2),0)))

The above formula will the location in the second worksheet if I could not find the location in the first worksheet and so on.


View attachment 9812

Kind regards

Saba

Hi, I used the other example provided, but I am still going to try this later because it is always good to learn different ways to approach a problem. Thank you so much for taking the time to answer my question!

 

[StephenCrump]

So locations would be like store 500 to 515 on the first sheet, store 516-530 on the second, and so on.

So you could replace the location table with a simple VLOOKUP:

Match across multiple sheets.xlsx
	A	B	C	D	E
1				SheetLookup
2	Location	534		500	GSP D18
3	Month	Feb 2020		516	GSP D35
4	Sheet	'GSP D110'!		531	GSP D110
5	Value	7777		546	GSP D121
MASTER

Cell Formulas
Range	Formula
B4	B4	="'"&VLOOKUP(B2,SheetLookup,2,1)&"'!"
B5	B5	=INDEX(INDIRECT(B4&"$B$5:$BQ$17"),MATCH($B$2,INDIRECT(B4&"A$5:$A$17"),),MATCH($B$3,INDIRECT(B4&"B$3:$BQ$3"),))

Named Ranges
Name	Refers To	Cells
SheetList	=MASTER!$E$2:$E$5	B4
SheetLookup	=MASTER!$D$2:$E$5	B4

Or even more directly:
B4: ="'"&INDEX(SheetList,MEDIAN(1,4,INT((B2-486)/15)))&"'!"

(For simplicity, I haven't error-proofed either formula here, but you should)

The only reason I can see having this issue is less stores in one district compared to another. How would the formula change to accommodate that?

This would cause a problem only if
- you didn't find a match in the smaller range actually used, and
- you did find a spurious match in the unused range.

Using your example, this would mean the store could not be found in rows 5 to 15, but could be found in row 16 or row 17. My guess is that you won't be using rows 16 and 17, so this won't be an issue in practice.

 

[Lalalauren191]

This would cause a problem only if
- you didn't find a match in the smaller range actually used, and
- you did find a spurious match in the unused range.

Using your example, this would mean the store could not be found in rows 5 to 15, but could be found in row 16 or row 17. My guess is that you won't be using rows 16 and 17, so this won't be an issue in practice.
[/QUOTE]

So technically I would be using rows 16 and 17. I am using a work computer, so I cant put a picture on here, but I can draw out what the sheet looks like as an example of why the set ranges might work for 3 sheets and not the 4th.

Sheet 1 to 3 might look like this, with the exception that store numbers would be different per sheet:

Row 5 - Store 505
Row 6 - Store 426
Row 7 - Store 1515
Row 8 - Store 504
Etc through row 17
Row 18 - Blank
Row 19 - Store 505 (these contain different data trends)
Row 20 - Store 426
Etc through row 26
and then more data 3 more times.

Sheet 4 might look like this:

Row 5 - Store 776
Row 6 - Store 668
Row 7 - Store 1466
Row 8 - Store 502
Etc through row 15
Row 16 - Blank
Row 17 - Store 505 (these contain different data trends)
Row 18 - Store 426
Etc through row 24
and then more data 3 more times.

Essentially each sheet is made up of these long trends of data, but each piece of data has it's own trend, so there are different ranges per trend, which I successfully used your formula to be able to look this data up on the main dashboard by changing the store and month drop downs (so seriously, thank you!), but i was curious what I would do if the store ranges are different? Currently I have 12 stores in each sheet, so the ranges match, but I may have to make this worksheet for others that have 12 on one sheet and 10 on another.

I hope all that made sense, and I again appreciate you taking the time.