Index and Match Question using Dates Multiple Criteria and Return Value

[EmHuuBub2019]

Hi, I am working on an Index Match function whereby I input a date in one field and a text in another field and it should return the total sales for a product for that particular month. This should bring back a value if the conditions are met. I need to have multiple criteria because my formula is showing March 1, 2018 but I want it to bring back total March sales. The formula I currently have is below. Therefore I used an "&" but I do not think this is correct because you can't use a date (March) and text field (MTD) together... =INDEX('[Wkly Orders & Sales Actuals, 2018.xls]LE & Budget'!$E$15:$CA$15,MATCH( $A$8, '[Wkly Orders & Sales Actuals, 2018.xls]LE & Budget'!$E$2:$CA$2,0)) Any help is appreciated to get this to work. Thanks.

 

[DanteAmor]

Try this:

=SUMPRODUCT(('[Wkly Orders & Sales Actuals, 2018.xls]LE & Budget'!E2:CA2>=A8)*('[Wkly Orders & Sales Actuals, 2018.xls]LE & Budget'!E2:CA2<=EOMONTH(A8,0))*('[Wkly Orders & Sales Actuals, 2018.xls]LE & Budget'!E15:CA15))

 

[EmHuuBub2019]

Hi, I am working on an Index Match function whereby I input a date in one field and a text in another field and it should return the total sales for a product for that particular month. This should bring back a value if the conditions are met. I need to have multiple criteria because my formula is showing March 1, 2018 but I want it to bring back total March sales. The formula I currently have is below. Therefore I used an "&" but I do not think this is correct because you can't use a date (March) and text field (MTD) together... =INDEX('[Wkly Orders & Sales Actuals, 2018.xls]LE & Budget'!$E$15:$CA$15,MATCH( $A$8, '[Wkly Orders & Sales Actuals, 2018.xls]LE & Budget'!$E$2:$CA$2,0)) Any help is appreciated to get this to work. Thanks.

-The answer you provided does not work. I have two different spreadsheets. My formula which requires the data for Mar-2018 MTD date below is bringing back the data for 2-Mar 2018 which is not what I want. I cannot use March in the formula because that is a date field and MTD because that is a text field together as stated earlier. I hope this clarifies what I need.

WK1	WK2	WK3	WK4	WK5	MTD
2-Mar-18	9-Mar-18	16-Mar-18	23-Mar-18	30-Mar-18	Mar-18

<colgroup><col width="93" style="width: 70pt; mso-width-source: userset; mso-width-alt: 3299; mso-outline-level: 1;"> <col width="93" style="width: 70pt; mso-width-source: userset; mso-width-alt: 3299; mso-outline-level: 1;"> <col width="93" style="width: 70pt; mso-width-source: userset; mso-width-alt: 3299; mso-outline-level: 1;"> <col width="93" style="width: 70pt; mso-width-source: userset; mso-width-alt: 3299; mso-outline-level: 1;"> <col width="93" style="width: 70pt; mso-width-source: userset; mso-width-alt: 3299; mso-outline-level: 1;"> <col width="93" style="width: 70pt; mso-width-source: userset; mso-width-alt: 3299;"> <tbody>
</tbody>

 

[EmHuuBub2019]

Please see my answer below because unfortunately your formula does not work. I provided the copy of the field that I need. Below the header is data. The formula is used in another spreadsheet to match the March date and bring back the value that is in the March MTD row for the specific part # that I need.Thanks.

 

[DanteAmor]

You could upload a copy of your file, with a couple of examples of what you have and what you expect results; to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.