title: Search Array from a filepath in a predefined cell to use with Index/Match formula
body: <p>I have a filepath that changes each month based on the date so I have a formula that sets the filepath in cell C1 =&quot;C:/User/022022 (this becomes 032022 in March and so on)/Febuary (This becomes March and so on).xlsx&quot;
  The formula here uses EOMONTH to get the previous months name in the file path</p>
  <p>essentially what I need this to do is the user chooses a clients name from A58. Then click a button to import all rows associated with this client from the above document into this tool. Due to the amount of data pulling through I have opted for Index arguement over Vlookup.</p>
  <p>I now need a way to search an array from this filepath using index/small AND to run this using a macro</p>
  <p>Im pretty certain I have the Macro sorted but I cant figure out the formula.</p>
  <p>This is what I have so far</p>
  <p>Cell C1 =</p>
  <p><em>=&quot;C/User/H88RRY/documents/&quot;&amp;TEXT(E58,&quot;yyyy&quot;)&amp;&quot;/&quot;&amp;TEXT(EOMONTH(E58,-2)+1,&quot;yyyymm&quot;)&amp;&quot;/[Management &quot;&amp;TEXT(EOMONTH(E58,-2)+1,&quot;mmmm&quot;)&amp; &quot; Month End Data.xlsx]worksheet'!&quot;</em></p>
  <p>Cell 61 (which I need the index for) =</p>
  <p><em>=IFERROR(INDEX(INDIRECT(&quot;'&quot; &amp; E1 &amp; &quot;$E$1:$E$350&quot;), SMALL(IF($A$58=(INDIRECT(&quot;'&quot; &amp; E1 &amp; &quot;$B$1:$B$350&quot;, ROW(INDIRECT(&quot;'&quot; &amp; E1 &amp; &quot; &quot;$B$1:$B$350&quot;)-ROW(INDIRECT(&quot;'&quot; &amp; E1 &amp; &quot;$B$1&quot;)+1),ROW(1:1))),&quot;&quot;)</em></p>
  <p>E1:E350 (on the sheet i need data from) is a bunch of names associated with the client(s), A58 (on the tool) contains a clients name from a drop down list, this is also found in B1:B350 from the table I want to get data from</p>
  <p>The formula works if I remove the INDIRECT arguement so I know that works.</p>
  <p>Any help will be greatly apreciated.</p>
  <p>Please and thank you</p>