INDEX MATCH (or X Lookup) by current Month

[PaulyK]

Hi,

I am creating a Status Report which shows different data from a series of separate spreadsheets. I am trying to show the result, for a specific project, in a specific month. The months are in the Column header only, which seems to be the challenge. I have tried an INDEX MATCH with an additional MATCH and even an XLOOKUP (although I haven't really used the formula before) but both return N/A.

Ideally I would like to use =Now() or similar to display the current Month (as opposed to the column I have used with the month in this example) as part of this as I would like the formula to update each month. Not sure how easy that is?

Suggestions?

Paul



Summary (in a separate Status Report sheet)

RAT Summary Example .xlsm
	J	K	L	M
3	Project	Updated (Yes/No)	Month	Method
4	Project 1	#N/A	Apr-22	INDEX MATCH MATCH
5	Project 1	#N/A	Apr-22	XLOOKUP
Non-Compliance

Cell Formulas
Range	Formula
K4:K5	K4	=INDEX(tblNonCom16[[Apr-22]:[Aug-22]], MATCH([@Project],tblNonCom16[Name],0), MATCH([@Month],tblNonCom16[[#Headers],[Apr-22]:[Aug-22]],0))

Data Table

RAT Summary Example .xlsm
	A	B	C	D	E	F	G
3	Project ID	Name	Apr-22	May-22	Jun-22	Jul-22	Aug-22
4	1001	Project 1	Yes	Yes	Yes	Yes	Yes
5	1002	Project 2	Yes	Yes	Yes	Yes	No
6	1003	Project 3	Yes	Yes	Yes	Yes	Yes
7	1004	Project 4	Yes	Yes	No	No	No
8	1005	Project 5	No	No	No	No	No
9	1006	Project 6	Yes	Yes	Yes	Yes	Yes
10	1007	Project 7	Yes	No	No	No	No
11	1008	Project 8	Yes	Yes	Yes	No	No
12	1009	Project 9	Yes	Yes	Yes	Yes	Yes
13	1010	Project 10	Yes	Yes	Yes	Yes	Yes
14	1011	Project 11	Yes	Yes	Yes	Yes	Yes
15	1012	Project 12	No	No	No	No	No
16	1013	Project 13	No	Yes	Yes	Yes	Yes
17	1014	Project 14	Yes	Yes	No	No	No
18	1015	Project 15	Yes	Yes	Yes	Yes	Yes
19	1016	Project 16	Yes	Yes	Yes	Yes	Yes
20	1017	Project 17	Yes	Yes	Yes	Yes	Yes
21	1018	Project 18	No	No	No	No	No
Non-Compliance

Cell Formulas
Range	Formula
C4:C21	C4	=IF(COUNTIFS(TblTime[[PROJECT NAME]:[PROJECT NAME]],$B4,TblTime[Apr-22],">0"),"Yes","No")
D4:D21	D4	=IF(COUNTIFS(TblTime[[PROJECT NAME]:[PROJECT NAME]],$B4,TblTime[May-22],">0"),"Yes","No")
E4:E21	E4	=IF(COUNTIFS(TblTime[[PROJECT NAME]:[PROJECT NAME]],$B4,TblTime[Jun-22],">0"),"Yes","No")
F4:F21	F4	=IF(COUNTIFS(TblTime[[PROJECT NAME]:[PROJECT NAME]],$B4,TblTime[Jul-22],">0"),"Yes","No")
G4:G21	G4	=IF(COUNTIFS(TblTime[[PROJECT NAME]:[PROJECT NAME]],$B4,TblTime[Aug-22],">0"),"Yes","No")
A4:A21	A4	=IFERROR(INDEX(TblProjAlloc[PROJECT NUMBER],MATCH([@Name],TblProjAlloc[PROJECT NAME],0))," ")

Named Ranges
Name	Refers To	Cells
'Project allocation'!_FilterDatabase	='Project allocation'!$A$11:$B$50	A4:A21

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C4:G21	Cell Value	="No"	text	NO
C4:G21	Cell Value	="Yes"	text	NO

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe

=INDEX(tblNonCom16[[Apr-22]:[Aug-22]], MATCH([@Project],tblNonCom16[Name],0), MATCH(TEXT([@Month],"mmm-yy"),tblNonCom16[[#Headers],[Apr-22]:[Aug-22]],0))

 

[PaulyK]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe

=INDEX(tblNonCom16[[Apr-22]:[Aug-22]], MATCH([@Project],tblNonCom16[Name],0), MATCH(TEXT([@Month],"mmm-yy"),tblNonCom16[[#Headers],[Apr-22]:[Aug-22]],0))

Ah yes, sorry I hadn't done that. I'm using 365 on Windows

 

[Fluff]

Thanks for that.
Did the suggestion I made work?

 

[PaulyK]

Thanks for that.
Did the suggestion I made work?

It has thank you. I did get N/A to start with but I discovered that the problem is that the Column Headers despite being Custom Formatted as MMM-YY did not match my Date cell which was 01/04/22 for April. it was Formatted the same but the header column header was Apr-22.
My initial attempt at a formula infact worked as well if I pasted the column header into the MATCH Lookup cell.

Any idea if it is possible to not have a referenced cell for the Month - i.e if it is currently April, the April value is displayed (and then will update each month) It isn't a necessity though

 

[Fluff]

The headers in a table are always text so cell format is normally irrelevant, which is why I suggested using the TEXT function.
You can replace [@Month] in the formula with TODAY() although the formula will then be volatile.

 

[PaulyK]

The headers in a table are always text so cell format is normally irrelevant, which is why I suggested using the TEXT function.
You can replace [@Month] in the formula with TODAY() although the formula will then be volatile.

ah I see. Learn something new every day! That makes sense.

Yes, I did try using =TODAY() previously (although aware it is Volatile) What is the 'risk' of using? Bearing in mind that the status report i will be using will only ever be required to show the current month's status? I am guessing that if someone saved a copy, it would change all the time?

 

[Fluff]

What is the 'risk' of using?

It just depends on the workbook. Try using it & if it doesn't cause any problems you're ok.