Dynamic Index/Match and HLookup Query Based on Column Headers

[emmarahudson]

Hello Everybody

Please see below a scaled down version of a report I'm working on. What I want to do is write a formula in columns B and C which will pull through the values in columns G and J (respectively). So, B3 = 0, B4 = 7, B5 = 9, B6 = 15 (and so on for columns C/I).

I have written a very basic Index/Match using the column headers but I'm now a bit lost. I think I may need to use HLookup as well but I'm not sure how to incorporate it. Can anybody help me?

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1		Sales Inc FOC	Purchases		Sales	Sales	Sales Inc FOC	Purchases	Purchases	Purchases
2		Overall	Overall		Jan	Feb	Overall	Jan	Feb	Overall
3	Alpha	0			0	0	0	1	2	3
4	Beta	0			3	4	7	1	1	2
5	Gamma	0			7	2	9	0	0	0
6	Delta	0			6	9	15	7	4	11
Sheet1

Cell Formulas
Range	Formula
B3	=INDEX($E$1:$J$6,MATCH($B$1,$E$1:$J$1,0),MATCH($B$2,$E$2:$J$2,0))
B4	=INDEX($E$1:$J$6,MATCH($B$1,$E$1:$J$1,0),MATCH($B$2,$E$2:$J$2,0))
B5	=INDEX($E$1:$J$6,MATCH($B$1,$E$1:$J$1,0),MATCH($B$2,$E$2:$J$2,0))
B6	=INDEX($E$1:$J$6,MATCH($B$1,$E$1:$J$1,0),MATCH($B$2,$E$2:$J$2,0))

 

[sheetspread]

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1		Sales Inc FOC	Purchases		Sales	Sales	Sales Inc FOC	Purchases	Purchases	Purchases
2		Overall	Overall		Jan	Feb	Overall	Jan	Feb	Overall
3	Alpha	0	3		0	0	0	1	2	3
4	Beta	7	2		3	4	7	1	1	2
5	Gamma	9	0		7	2	9	0	0	0
6	Delta	15	11		6	9	15	7	4	11
Sheet2

Cell Formulas
Range	Formula
B3	{=INDEX($E3:$J3,MATCH(B$1&B$2,$E$1:$J$1&$E$2:$J$2,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[emmarahudson]

Thank you so much. I was trying a similar array formula earlier but I'd indexed the wrong cells! Thank you so much, you've just cut my Saturday afternoon work in half.

 

[emmarahudson]

Hi Sheetspread

I have another query which is sort of related to the one you helped me with on Saturday.

I've got a large amount of data on a tab titled 'Data' (surprise surprise!). I've cut it down to the parts that are relevant (see below).

Excel 2010
	H	AD	AE	AF	AG	AH	AI
1		ASP w/o FOC JAN 2016 - MAY 2016	ASP w/o FOC JAN 2016 - MAY 2016	ASP w/o FOC JAN 2016 - MAY 2016	ASP w/o FOC JAN 2016 - MAY 2016	ASP w/o FOC JAN 2016 - MAY 2016	ASP w/o FOC JAN 2016 - MAY 2016
2		JAN 2016	FEB 2016	MAR 2016	APR 2016	MAY 2016	Overall Result
3	Material
4	M200661		0				0
5	M200841	0					0
6	M201011					13076.84	13076.84
7	M201011		11999.92				11999.92
8	M201011			12499.91			12499.91
9	M201011		0			0	0
10	M201011		0				0
11	M201011		11223.22	0	0.01	0	11813.91526
Data

On another tab titled 'Summary' I am trying to find the lowest selling price within the above set of data for each product (excluding zero values). The product list in the 'Summary' tab is located in column C. To find the lowest value for each product, I have written an array formula but I've had to specify the column which it is to look in. What I'd like to do, is replace the "IF(Data!AA:AA>0,Data!AA:AA)" part of the formula with something that uses the headers in rows 1 and 2 of the 'Data' tab. The reason I need to use the header titles instead of specifying the column is the data moves every month as I add more to the spreadsheet. I know that I could just paste the data I need into another tab, but I'd really like to avoid this if possible because it creates more work.

Excel 2010
	C	CV	CW	CX	CY	CZ	DA	DB	DC	DD	DE	DF	DG
11		LSP	LSP	LSP	LSP	LSP	LSP	LSP	LSP	LSP	LSP	LSP	LSP
12	Material	Jan-16	Feb-16	Mar-16	Apr-16	May-16	Jun-16	Jul-16	Aug-16	Sep-16	Oct-16	Nov-16	Dec-16
396	M201011	41999.76	70883.52	149.35	12499.91	11223.22	12499.91	0.01	11813.92	11813.92
Summary

Cell Formulas
Range	Formula
CV396	{=IFERROR(IF(MIN(IF(Data!$H:$H=Summary!$C396,IF(Data!AA:AA>0,Data!AA:AA)))=0,"",MIN(IF(Data!$H:$H=Summary!$C396,IF(Data!AA:AA>0,Data!AA:AA)))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[kvsrinivasamurthy]

Try this
ARRAY formula in CV396, then dragged across

=IFERROR(SMALL(IF((data!$H$4:$H$11=$C396)*(INDEX(data!$AD$4:$AI$11,,MATCH(CV$12,data!$AD$2:$AI$2,0))>0),INDEX(data!$AD$4:$AI$11,,MATCH(CV$12,data!$AD$2:$AI$2,0)),""),1),"")

 

[sheetspread]

Excel 2010
	H	AD	AE	AF	AG	AH	AI
2		Jan-16	Feb-16	Mar-16	Apr-16	May-16	Overall Result
3	Material
4	M200661		0				0
5	M200841	0					0
6	M201011					13076.84	13076.84
7	M201011		11999.92				11999.92
8	M201011			12499.91			12499.91
9	M201011		0			0	0
10	M201011	41999.76	0				0
11	M201011		11223.22	0	0.01	0	11813.91526
Data

Excel 2010
	C	CV	CW	CX	CY	CZ	DA	DB	DC	DD	DE	DF	DG
11		LSP	LSP	LSP	LSP	LSP	LSP	LSP	LSP	LSP	LSP	LSP	LSP
12	Material	1-Jan	1-Feb	1-Mar	1-Apr	1-May	1-Jun	1-Jul	1-Aug	1-Sep	1-Oct	1-Nov	1-Dec
396	M201011	41999.76	11223.22	12499.91	0.01	13076.84
Summary

Cell Formulas
Range	Formula
CV396	{=MIN(IF(Data!$H$4:$H$11=$C396,IF(INDEX(Data!$AD$4:$AH$11,,MATCH(CV$12,Data!$AD$2:$AH$2,0))<>0,INDEX(Data!$AD$4:$AH$11,,MATCH(CV$12,Data!$AD$2:$AH$2,0)))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

works too

 

[emmarahudson]

Hiya, thanks for coming back to me, it's really appreciated. The problem I'm having is that I need the 'MATCH(CV$12,Data!$AD$2:$AH$2,0)' part of the formula to look at rows 1 and 2. The data I am working with has lots of different headers in row 1 (the table I pasted into the forum is scaled down). So, if I use January as an example, I'd like the formula to return the lowest value if row 1 contains "ASP w/o FOC" and if row 2 contains "JAN 2016". At the moment, the formula is just looking at the date in row 2. Is that something that can be done? I've played around with it myself and I get certain parts of it to function, but I just can't seem to bring it all together.

 

[sheetspread]

Something like this?

Excel 2010
	C	CV	CW	CX	CY	CZ	DA	DB	DC	DD	DE	DF	DG
11		ASP w/o FOC JAN 2016 - MAY 2016	ASP w/o FOC JAN 2016 - MAY 2016	ASP w/o FOC JAN 2016 - MAY 2016	ASP w/o FOC JAN 2016 - MAY 2016	LSP	LSP	LSP	LSP	LSP	LSP	LSP	LSP
12	Material	1-Jan	1-Feb	1-Mar	1-Apr	1-May	1-Jun	1-Jul	1-Aug	1-Sep	1-Oct	1-Nov	1-Dec
396	M201011	41999.76	11223.22	12499.91	0.01
Summary

Cell Formulas
Range	Formula
CV396	{=MIN(IF(Data!$H$4:$H$11=$C396,IF(INDEX(Data!$AD$4:$AH$11,,MATCH(CV$11&CV$12,Data!$AD$1:$AH$1&Data!$AD$2:$AH$2,0))<>0,INDEX(Data!$AD$4:$AH$11,,MATCH(CV$11&CV$12,Data!$AD$1:$AH$1&Data!$AD$2:$AH$2,0)))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[emmarahudson]

Hiya, thanks for coming back to me. I've just managed to sort it. Solution below in case anyone else ever needs to resolve a query like this. Thank you so much for all your help, Sheetspread

=IFERROR(SMALL(IF((Data!$H$4:$H$10000=$C396)*(INDEX(Data!$AD$4:$XFD$10000,,MATCH("*ASP*"&CV$12,Data!$AD$1:$XFD$1&Data!$AD$2:$XFD$2,0))<>0),INDEX(Data!$AD$4:$XFD$10000,,MATCH("*ASP*"&CV$12,Data!$AD$1:$XFD$1&Data!$AD$2:$XFD$2,0)),""),1),"")