Date matching when doing lookup - help needed

[ErikNolander]

Hi all,

I'm trying to combine a lookup formula with a date matching query, but have not managed to make this work so any suggestions are welcome. For the attached files, I want to only show results with dates in October 2020.

I collate estate inspections made by our housing teams in a separate sheet, and then match these with the actual property listings to show progress. Traditionally, the inspection results have been manually separated into months, but I would like to automate this process, and this is where the problem arises. The attached files are a cutdown version of my dataset to just show the issues.

In the Master Monitor Test book, column B does a simple XLOOKUP from Cleaning Survey extract 0.5 - this works fine, I'm ok with the format, but it pulls through every match disregarding dates. Column C is my latest attempt at adding a date matching component, but as can be seen, this does not work at all. 

Any help or suggestions are really welcomed. If it means VBA, it's not something I'm super comfortable with, so I would prefer a formula based solution. However, if VBA is the way, who am I to say? :) The only constraint I have is the report output format (ie, the Cleaning table), this is fixed by the business, but I'm open to suggestions.

Many thanks,

Erik Nolander

[#2 · Accepted solution]

@ErikNolander 

Erik, perhaps you mean something like

=XLOOKUP(
    [@[Scheme/Estate]]&"="&MONTH($B$4),
    'Cleaning Survey extract 0.5.xlsx'!tbl_survey[Scheme Name] &
          "=" &
          MONTH('Cleaning Survey extract 0.5.xlsx'!tbl_survey[Date of inspection]),
    'Cleaning Survey extract 0.5.xlsx'!tbl_survey[Internal],
    "not rated"
)

[#3]

@Sergei Baklan thank you - this works perfectly! This is really going to save time and improve our workflow.

Really grateful for your quick response and help!

Regards,

Erik

[#4]

@ErikNolander , you are welcome, glad to help