Lookup multiple criteria

[Joey_Ng]

Hello Experts,

Needing your help please with lookup using multiple criteria's:

Loading Google Sheets

The above link has the workbook. I am trying to look up the values for each of the cells in B2:E4 for sheets "Cat B" and "Cat C" looking up values in "Sheet1" using the 3 criterias: category, week number & type. Please help with:
1) what's the best formula to look up those 3 criterias in the workbook? Can you help with the formula answer?
2) can you help with how to best combine "sheetname + substitute formula + match formula" into a cell address?

My workings below:

Working	Value	Formula
row number for Cat B >	4	=MATCH(A1,Sheet1!A:A,0)
Column letter for wk1 (weeknumber) >	B	=SUBSTITUTE(ADDRESS(1,MATCH(B1,Sheet1!1:1,0),4),"1","")
Adding 2 to the above (address) formula to get the end section. That is each week always have 3 columns containing hat, shirt & shoe >	D	=SUBSTITUTE(ADDRESS(1,(MATCH(B1,Sheet1!1:1,0)+2),4),"1","")
knowing above I could use Xlookup to return 457 > but I want to include the formulas for the moving columns for the week numbers	457	=XLOOKUP(A2,Sheet1!B2:D2,Sheet1!B4:D4,,0)
I tried the formula on the right but couldn't get the filename right using CONCAT. Not sure how to incoporate sheetname+substitute formula + match formula) as a cell address		=XLOOKUP(A2,CONCAT("Sheet1!",SUBSTITUTE(ADDRESS(1,MATCH(B1,Sheet1!1:1,0),4),"1",""),2):CONCAT("Sheet1!",SUBSTITUTE(ADDRESS(1,(MATCH(B1,Sheet1!1:1,0)+2),4),"1",""),4),"1",""),2),CONCAT("Sheet1!",SUBSTITUTE(ADDRESS(1,MATCH(B1,Sheet1!1:1,0),4),"1",""),MATCH(A1,Sheet1!A:A,0)):CONCAT("Sheet1!",SUBSTITUTE(ADDRESS(1,(MATCH(B1,Sheet1!1:1,0)+2),4),"1",""),4),"1",""),MATCH(A1,Sheet1!A:A,0)),,0)

Thank You,

Joey

 

[JvdV]

Maybe the following does help:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Wk1			Wk2			Wk3			Wk4
2	Category	hat	shirt	shoe	hat	shirt	shoe	hat	shirt	shoe	hat	shirt	shoe
3	A	567	119	839	559	338	356	248	291	195	239	661	697
4	B	457	477	579	559	425	781	341	176	994	376	880	297
5	C	271	607	758	460	111	1000	738	404	247	180	566	603
6	D	911	149	794	887	892	708	495	525	626	945	853	711
7
8	B	Wk1	wk2	wk3	wk4
9	Hat	457	559	341	376
10	Shirt	477	425	176	880
11	Shoe	579	781	994	297
Sheet6

Cell Formulas
Range	Formula
B9:E11	B9	=INDEX(FILTER(FILTER($B$3:$M$6,$A$3:$A$6=$A$8),$B$2:$M$2=$A9),MATCH(B$8,TOROW($1:$1,1),0))

 

[Skybluekid]

Here is a version you want to have it on separate sheets. Had to cheat a little, by putting Wk1 in the three columns( this can be "disguised" by using white text)

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Wk1	Wk1	Wk1	Wk2	Wk2	Wk2	Wk3	Wk3	Wk3	Wk4	Wk4	Wk4
2	Category	hat	shirt	shoe	hat	shirt	shoe	hat	shirt	shoe	hat	shirt	shoe
3	A	567	119	839	559	338	356	248	291	195	239	661	697
4	B	457	477	579	559	425	781	341	176	994	376	880	297
5	C	271	607	758	460	111	1000	738	404	247	180	566	603
6	D	911	149	794	887	892	708	495	525	626	945	853	711
Sheet1

and the below is on the Cat B Sheet

Book1.xlsx
	A	B	C	D	E
1	B	Wk1	wk2	wk3	wk4
2	Hat	457	559	341	376
3	Shirt	477	425	176	880
4	Shoe	579	781	994	297
Cat B

Cell Formulas
Range	Formula
B2:E4	B2	=INDEX(Sheet1!$B$3:$M$6,MATCH($A$1,Sheet1!$A$3:$A$6,0),MATCH(B$1&$A2,Sheet1!$B$1:$M$1&Sheet1!$B$2:$M$2,0))

 

[Joey_Ng]

Thanks JvdV and Skybluekid. The answers doesn't seem to work. Please note below:
1) The lookup sheets (Cat B & cat C) has its own sheets (ie, cannot add to the reference "Sheet1")
2) the reference Sheet1 is a weekly extract so cannot make changes to it. Also, for example B1:D1 (wk1 section) is merged cells so cannot manually unmerge for each section.
Any further help would be much appreciated.

Many Thanks,

Joey

 

[Skybluekid]

Hi, try the below

Book1.xlsx
	G	H	I	J	K
2	B	Wk1	Wk2	Wk3	Wk4
3	Hat	457	559	341	376
4	Shirt	477	425	176	880
5	Shoe	579	781	994	297
Cat B

Cell Formulas
Range	Formula
H3:K5	H3	=INDEX(OFFSET(Sheet1!$A$3,,MATCH(H$2,Sheet1!$B$1:$M$1,0),4,3),MATCH($A$1,Sheet1!$A$3:$A$6,0),MATCH($A2,Sheet1!$B$2:$D$2,0))

 

[Joey_Ng]

Hi, try the below

Book1.xlsx
	G	H	I	J	K
2	B	Wk1	Wk2	Wk3	Wk4
3	Hat	457	559	341	376
4	Shirt	477	425	176	880
5	Shoe	579	781	994	297
Cat B

Cell Formulas
Range	Formula
H3:K5	H3	=INDEX(OFFSET(Sheet1!$A$3,,MATCH(H$2,Sheet1!$B$1:$M$1,0),4,3),MATCH($A$1,Sheet1!$A$3:$A$6,0),MATCH($A2,Sheet1!$B$2:$D$2,0))

Hi Skybluekid,

Thanks very much. This works well.

Thanks,

Joey

 

[Skybluekid]

You are very welcome.