Using Index Match to find the month the value occurs in

[erniepoe]

Hi All,

i am trying to find the specific month (row 1 is dates, Jan thru Sept) that a specific value (in this case "vacant") occurs for a specific property number in column A.

I tried using index/match in a reverse/backward manner in order to do so, but the formula is not working. Any idea how I can flip this formula so the output is the month in which "vacant" occurs?

Thanks so much for any help!


=INDEX('[Sheet1.xlsx]Vacancy Status'!$E:$M,MATCH("0150",'[Sheet1.xlsx]Vacancy Status'!$A:$A,0),MATCH("Vacant",'[Sheet1.xlsx]Vacancy Status'!$E$1:$M$1,0))

 

[Cubist]

Do you mean something like this?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		January	February	March	April	May	June	July	August	September	October	November	December
2	a
3	b
4	c						Vacant
5	d			Vacant
6	e
7	f
8	g
9
10	June
11	March
Sheet2

Cell Formulas
Range	Formula
A10:A11	A10	=TOCOL(IFS(B2:M8="Vacant",B1:M1),2)
Dynamic array formulas.

 

[erniepoe]

yes, exactly, thank you so much! How can i augment that formula to look for a specific property number within that matrix?

 

[Cubist]

How about this?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		January	February	March	April	May	June	July	August	September	October	November	December
2	a
3	b
4	c						Vacant
5	d			Vacant
6	e
7	f
8	g
9
10	a	None
11	b	None
12	c	June
13	d	March
14	e	None
15	f	None
16	g	None
Sheet2

Cell Formulas
Range	Formula
B10:B16	B10	=XLOOKUP("Vacant",FILTER($B$2:$M$8,$A$2:$A$8=A10),$B$1:$M$1,"None")

 

[erniepoe]

yes, that worked perfectly! Thank you!

 

[Cubist]

yes, that worked perfectly! Thank you!

You're welcome. Note that this only returns the first vacant month.