VLOOKUP using a date as the search key (but ignoring the day)-CodePudding

The range A2:A contains months where each month uses the first day of the month. Users are allowed to input any day within that month when entering their data. The range of user input dates is defined by the named range "EAR_DATES_RNG". Each month has a corresponding summary of earnings for that month.

So here is an example of what this might look like:

User Input:
1/11/2017 - $100
2/9/2017 - $250
3/21/2017 - $500

Summary:
1/1/2017 - $100
2/1/2017 - $250
3/1/2017 - $500

Previously, I had a note forcing users to also use the first day of the month for their inputs, however I would like to free that up. However, this has caused my VLOOKUP() to stop working as it was previously using the summary date to find the corresponding user input:

=ARRAY_CONSTRAIN(ARRAYFORMULA(IFERROR(VLOOKUP(A2:A&" "&$A$1,{EAR_DATES_RNG&" "&EAR_NAMES_RNG,EAR_AMOUNTS_RNG},2,false),"")), IF(COUNT(A2:A)=0,1,COUNT(A2:A)),1)

How could I instead tell VLOOKUP to ignore the day of the month?

Or maybe there is a way I could somehow convert the user input dates to the first of the month using EOMONTH() tricks when I create the array in the formula, however maybe the fact that I use a named range will pose another problem for me to overcome.

I can however, see a solution already that would require a helper column that converts all the dates in EAR_DATES_RNG and then the formula creates the array using that converted range instead, but I would like to avoid a helper column if possible.

Appreciate any help I can get with this!

Screenshot examples:

OUTPUT:

[ VLOOKUP SHEET1

INPUT:

January and March user inputs do not use the first day of the month, while February and April do. So as a result, in the 1st image, January and March values are blank in Column C, while February and April are showing up.

My VLOOKUP() is in Column C of the first image and is supposed to be grabbing the corresponding values of Column G in the second image.

CodePudding user response：

try:

=ARRAY_CONSTRAIN(ARRAYFORMULA(IFERROR(VLOOKUP(TEXT(A2:A, "m/1/e")*1&" "&$A$1, 
 {EAR_DATES_RNG&" "&EAR_NAMES_RNG, EAR_AMOUNTS_RNG}, 2, 0))), 
 IF(COUNT(A2:A)=0, 1, COUNT(A2:A)), 1)

or:

=ARRAY_CONSTRAIN(ARRAYFORMULA(IFERROR(VLOOKUP(A2:A&" "&$A$1, 
 {TEXT(EAR_DATES_RNG, "m/1/e")*1&" "&EAR_NAMES_RNG, EAR_AMOUNTS_RNG}, 2, 0))), 
 IF(COUNT(A2:A)=0, 1, COUNT(A2:A)), 1)

CodePudding user response：

Here is a script version of the function you want.

function firstOfTheMonth() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();

  //Gets the values of column A until the last row with data
  var range = sheet.getRange(2,1, sheet.getLastRow() - 1, 1);

  //Changes the date values of column A into MM-YYYY-1
  // You need to define your TimeZone on Utilities.formatDate(new Date(y), "YOUR TIMEZONE HERE", "MM-1-yyyy")
  var val = range.getValues();
  var newval = val.map(x => x.map(y => Utilities.formatDate(new Date(y), "GMT 8", "MM-1-yyyy")));

  console.log(newval);
  range.setValues(newval); 
}

You can manually add the function on an onEdit Trigger so that the function will automatically run whenever an edit is made on the spreadsheet.

Before:

After the script was ran:

Reference:

https://developers.google.com/apps-script/reference/utilities/utilities#formatDate(Date,String,String)