Excel lookup index that can return multiple results-CodePudding

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?

My spreadsheet looks like this:

FinalResult tab:
A             V
Index  ....   ErrorState
A1234  ....   No results
A3400  ....   #SPILL!

with V formula being: =FILTER(List_State_11.18.2021!A2:R81,List_State_11.18.2021!A2:A81=FinalResult!A2, "No results")

And the List_State_11.18.2021 tab looks like this:

    A          J
    Device ... Display_Text
    A1234  ... Not Found
    A2345  ... Fault
    A3400  ... Not Found
    ...

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.

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?

I checked, and the #spill! is not one with multiple matches. The index for the spill result is not in the List_state tab.

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. filter multiple

CodePudding user response：

Something like the following should work:

=LET(data, List_State_11.18.2021!J:J, filterlist, List_State_11.18.2021!A:A, lookup, A2, TRANSPOSE(FILTER(data, filterlist=lookup, "No Results")))

For use with TEXTJOIN, you wouldn't need to transpose the results

=LET(data, List_State_11.18.2021!J:J, filterlist, List_State_11.18.2021!A:A, lookup, A2, results, FILTER(data, filterlist=lookup,"NA"), TEXTJOIN("; ", TRUE, results))