1 Reply Latest reply on Jul 30, 2013 7:34 AM by Jonathan Drummey

    Is there a way to replicate Excel's Index/Match functionality in Tableau?

    Paul McMurtry

      Hi,

       

      Is there a way to replicate Excel's Index/Match functionality in Tableau?

       

      I currently use a combination of Excel's Index and Match functions to detrermine the correct fiscal month for a given date using 2 different tables.  I want to do the same in Tableau.

       

      My first Excel table contains 3 columns and several rows.  The first column is a date field.  The second column is a revenue value.  The third column is the fiscal month of the date.

       

      My second Excel table contains my company's fiscal calendar.  This is the reference table that is used to dertermine the fiscal month of each date in the first table.  Please note that in FY14, our fiscal year starts on June 29, 2013 and ends on June 27, 2014.  The fiscal calendar table contains 3 columns with many rows.  The first column corresponds to the first day of the fiscal month.  The second column corresponds to the last day of the fiscal month.  The third column corresponds to the name of the fiscal month.

       

      I use Excel's Match function combined with Excel's Index function to determine the fiscal month that corresponds to the date in the first table. Please refer to the attached Excel file that contains the afforementioned tables and formulas.  Cells D2:D13 contain the Index/Match formula.

       

      In practice, the revenue table and fiscal calendar will be stored in two separate files.  Both files will be connected to my Tableau workbook and I would lke Tableau to automatically calculate the fiscal month for every date in the revenue table.  My goal is to be able to display revenue by fiscal period on a simple bar chart with revenue on the y-axis and the fiscal months on the x-axis.

       

      Does Tableau have a function or a combination of functions that could be used to calculate the fiscal month for the dates in my first table?

       

      Your help would be greatly appreciated.

       

      Regards,

       

      Paul