Need Formula Help Please

[Vike1979]

I need help with a formula that will return the month header (per row) where the price first changes. Shown below are examples of this Cell P is where I need the header month to show the first month with a increase or decrease. Column C is a base price.

View attachment 87155

 

[Fluff]

Hi & welcome to MrExcel.
How about

=INDEX(FILTER($D$5:$O$5,D6:O6<>C6),,1)

 

[Vike1979]

I only got it to give #CALC or return the wrong value. Will the Index/Filter return the Column header month where the price first changes? For the first line example, I'm trying to get it return Mar 2023 in column/cell P6.

 

[Fluff]

Will the Index/Filter return the Column header month where the price first changes?

Yes it will.
You should only get a #calc error if all the values in C6:O6 are the same, in which case you can use

=INDEX(FILTER($D$5:$O$5,D6:O6<>C6,"No change"),,1)

 

[Vike1979]

Yes it will.
You should only get a #calc error if all the values in C6:O6 are the same, in which case you can use

=INDEX(FILTER($D$5:$O$5,D6:O6<>C6,"No change"),,1)

Is there a way to show those month headers that don't change prices? Thank you for helping me. I still can't get it to return the Mar 23 header but progress is being made.

 

[Fluff]

I still can't get it to return the Mar 23 header

In that case can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Vike1979]

In that case can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Vike1979]

Formula Inquiry.xlsx
	A		C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1
2			*Example of formula I need to return a month header where the first price change occurs per row.
3
4			Base Month (C)	Need to show/return column header (Month/Year) where the first price change occurred. Each row(name/item #) is a different product												¯
5	Name		Dec 2022	Jan 2023	Feb 2023	Mar 2023	Apr 2023	May 2023	Jun 2023	Jul 2023	Aug 2023	Sep 2023	Oct 2023	Nov 2023	Dec 2023	Month Change	*Need Column to return header (month name of first price change)
6	Item 1		$12.00	$12.00	$12.00	$13.00	$13.00	$13.00	$13.00	$13.00	$13.00	$13.00	$13.00	$13.00	$13.00	Mar 2023 (result needed)	*Need formula to return Mar 2023 as the month of the price change
7	Item 2		$11.00	$11.00	$11.00	$11.00	$11.00	$11.00	$11.00	$11.00	$14.00	$14.00	$14.00	$14.00	$14.00	Aug 2023 (result needed)	*Need formula to return Aug 2023 as the month of the price change
8	Item 3		$9.00	$9.00	$9.00	$9.00	$9.00	$9.00	$9.00	$9.00	$9.00	$9.00	$8.00	$8.00	$8.00	Oct 2023 (result needed)	*Need formula to return Oct 2023 as the month of the price change
9	Item 4
10
11
12
13
PCR

 

[Fluff]

Thanks for that.
The formula I suggested works for me.

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2			*Example of formula I need to return a month header where the first price change occurs per row.
3
4			Base Month (C)	Need to show/return column header (Month/Year) where the first price change occurred. Each row(name/item #) is a different product												¯
5	Name		Dec-22	Jan-23	Feb-23	Mar-23	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23	Month Change
6	Item 1		12	12	12	13	13	13	13	13	13	13	13	13	13	01/03/2023
7	Item 2		11	11	11	11	11	11	11	11	14	14	14	14	14	01/08/2023
8	Item 3		9	9	9	9	9	9	9	9	9	9	8	8	8	01/10/2023
9	Item 4															No change
Main

Cell Formulas
Range	Formula
P6:P9	P6	=INDEX(FILTER($D$5:$O$5,D6:O6<>C6,"No change"),,1)

Can you post what you get

 

[Vike1979]

Thanks for that.
The formula I suggested works for me.

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2			*Example of formula I need to return a month header where the first price change occurs per row.
3
4			Base Month (C)	Need to show/return column header (Month/Year) where the first price change occurred. Each row(name/item #) is a different product												¯
5	Name		Dec-22	Jan-23	Feb-23	Mar-23	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23	Month Change
6	Item 1		12	12	12	13	13	13	13	13	13	13	13	13	13	01/03/2023
7	Item 2		11	11	11	11	11	11	11	11	14	14	14	14	14	01/08/2023
8	Item 3		9	9	9	9	9	9	9	9	9	9	8	8	8	01/10/2023
9	Item 4															No change
Main

Cell Formulas
Range	Formula
P6:P9	P6	=INDEX(FILTER($D$5:$O$5,D6:O6<>C6,"No change"),,1)

Can you post what you get

Please see below

Formula Inquiry.xlsx
	A		C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1
2			*Example of formula I need to return a month header where the first price change occurs per row.
3
4			Base Month (C)	Need to show/return column header (Month/Year) where the first price change occurred. Each row(name/item #) is a different product												¯
5	Name		Dec 2022	Jan 2023	Feb 2023	Mar 2023	Apr 2023	May 2023	Jun 2023	Jul 2023	Aug 2023	Sep 2023	Oct 2023	Nov 2023	Dec 2023	Month Change	*Need Column to return header (month name of first price change)
6	Item 1		$12.00	$12.00	$12.00	$13.00	$13.00	$13.00	$13.00	$13.00	$13.00	$13.00	$13.00	$13.00	$13.00	Mar 2023	*Need formula to return Mar 2023 as the month of the price change
7	Item 2		$11.00	$11.00	$11.00	$11.00	$11.00	$11.00	$11.00	$11.00	$14.00	$14.00	$14.00	$14.00	$14.00	Aug 2023	*Need formula to return Aug 2023 as the month of the price change
8	Item 3		$9.00	$9.00	$9.00	$9.00	$9.00	$9.00	$9.00	$9.00	$9.00	$9.00	$8.00	$8.00	$8.00	Oct 2023	*Need formula to return Oct 2023 as the month of the price change
9	Item 4
PCR

Cell Formulas
Range	Formula
P6:P8	P6	=INDEX(FILTER($D$5:$O$5,D6:O6<>C6,"No change"),,1)