Use INDIRECT combined with INDEX MATCH (multiple row criteria) to return values from specified sheet

[Bluefish74274]

I know its possible to lookup and return the value from a cell on a specified worksheet within a workbook. I've done this before using INDIRECT combined with INDEX MATCH. However I am only able to get this to work when matching 1 row and 1 column. Can this be done if I have 2 rows and 1 column to match or would I need to use a different function? Please let me know if example is needed.

Thanks in advance for any guidance.

 

[Bluefish74274]

I know its possible to lookup and return the value from a cell on a specified worksheet within a workbook. I've done this before using INDIRECT combined with INDEX MATCH. However I am only able to get this to work when matching 1 row and 1 column. Can this be done if I have 2 rows and 1 column to match or would I need to use a different function? Please let me know if example is needed.

Thanks in advance for any guidance.

Here is sample of spreadsheets. Image #1 --- Master, Image #2 & #3 ---Lookup sheets called Health and Beauty, respectively

 

[Peter_SSs]

Perhaps your sample is a bit brief, but I'm wondering if you need to check two columns. For your sample data & layout, what happens if you put this in cell C7 of 'Master'?

=FILTER(INDIRECT(B$4&"!C$3:N$10"),INDIRECT(B$4&"!A$3:A$10")=B$3,"")

 

[Bluefish74274]

Perhaps your sample is a bit brief, but I'm wondering if you need to check two columns. For your sample data & layout, what happens if you put this in cell C7 of 'Master'?

=FILTER(INDIRECT(B$4&"!C$3:N$10"),INDIRECT(B$4&"!A$3:A$10")=B$3,"")

Thank you so much Peter! This works great! Once caveate. In the sample data I sent, the lookup tabs have 2 lines for each category (sales, margin). The real scenario has 3 lines (sales, margin, margin rate) but I only want to return sales and margin lines to the master tab. The formula you provided brings all rows over to master. Can this formula be tweaked or is there another formula that to accomplish my goal?

Regards,
Nancy

 

[Peter_SSs]

Can this formula be tweaked or is there another formula that to accomplish my goal?

You could try the tweaked formula in C7 below or the replacement formula in C11 which could be copied down and see what suits you best (assuming they do what you want).
In either case you may need to adjust the ranges to match your data.

Bluefish74274.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N
3	Cosmetics
4	Beauty
5
6		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
7	Sales	2	5	9	6	3	5	2	2	5	1	3	6
8	Margin	3	2	1	7	5	2	7	9	7	1	6	4
9
10
11	Sales	2	5	9	6	3	5	2	2	5	1	3	6
12	Margin	3	2	1	7	5	2	7	9	7	1	6	4
13
Master

Cell Formulas
Range	Formula
C7:N8	C7	=TAKE(FILTER(INDIRECT(B$4&"!C$3:N$10"),INDIRECT(B$4&"!A$3:A$10")=B$3,""),2)
C11:N12	C11	=FILTER(INDIRECT(B$4&"!C$3:N$10"),(INDIRECT(B$4&"!A$3:A$10")=B$3)*(INDIRECT(B$4&"!B$3:B$10")=B11),"")
Dynamic array formulas.