title: Excel lookup index that can return multiple results
body: <p>I want to do an index lookup that can potentially return multiple results.  I'm trying filter, and that doesn't seem to be working.  Any suggestions?</p>
  <p>My spreadsheet looks like this:</p>
  <pre><code>FinalResult tab:
  A             V
  Index  ....   ErrorState
  A1234  ....   No results
  A3400  ....   #SPILL!
  </code></pre>
  <p>with V formula being: <code>=FILTER(List_State_11.18.2021!A2:R81,List_State_11.18.2021!A2:A81=FinalResult!A2, &quot;No results&quot;)</code></p>
  <pre><code>And the List_State_11.18.2021 tab looks like this:
  
      A          J
      Device ... Display_Text
      A1234  ... Not Found
      A2345  ... Fault
      A3400  ... Not Found
      ...
  </code></pre>
  <p>But every Device in List_state tab does not have Display_Text to find.  Ie. Not every Index on the FinalResult tab has a fault to find in List_state.  There's about 80 items in List_state with repeats for devices (multiple faults), and 300 indices in FinalResult with no repeats for index (exclusive).  I want to return all faults found for each index, if found.</p>
  <p>In my use of filter, it's finding all No results or #Spill!, with no other results.  How do I fix what I have, or what other method can I use to find no, one, or multiple faults in my List_state tab, and put it in my FinalResult tab?</p>
  <p>I checked, and the #spill! is not one with multiple matches.  The index for the spill result is not in the List_state tab.</p>
  <p>I've never done a lookup like this, so it's more than likely user error.  Usually I do a vlookup where it's finding one device result and not multiple results. When I googled it, it seemed to suggest filter, but I could be mistaken. I haven't seen an example where the multiple results returned/found are put in the one row/column and not spilling to a second column like I want. I'm not convinced filter and my formula is ultimately what I need.
  <a href="https://www.ablebits.com/office-addins-blog/2020/06/17/excel-filter-function/" rel="nofollow noreferrer">filter</a>
  <a href="https://www.get-digital-help.com/how-to-return-multiple-values-using-vlookup-in-excel/" rel="nofollow noreferrer">multiple</a></p>