Excel Index Match – Elenca tutti i risultati

Sto usando la seguente function Match Index per get il nome di un'azienda in cui i dati di spesa corrispondono a quelli di cui digito nella cella BF17.

=INDEX($AM$16:$BB$16,MATCH(BF17,AM17:BB17,0))

Quello che voglio essere in grado di fare è elencare più risultati all'interno della stessa cella e separarli con una virgola.

Qualcuno sa se questo è ansible e se così può qualcuno mi piacerebbe mostrarmi come?

Grazie

Codice:

Inserisci questo codice in un module nella cartella di lavoro:

 Public Function hLookupList(KeyVal, Vals As Range, Ret As Range) As String Dim i As Long Dim vw As Worksheet Dim rw As Worksheet Dim RetStr As String Application.Volatile True Set vw = Vals.Worksheet Set rw = Ret.Worksheet If Vals.Rows.Count > 1 Then hLookupList = "Too Many Value Rows Selected!" Exit Function End If If Ret.Rows.Count > 1 Then hLookupList = "Too Many Return Rows Selected!" Exit Function End If If Vals.Columns.Count <> Ret.Columns.Count Then hLookupList = "Value Range and Return Range must be the same size!" Exit Function End If For i = Vals.Column To Vals.Column + Vals.Columns.Count - 1 If vw.Cells(Vals.Row, i) = KeyVal Then RetStr = RetStr & rw.Cells(Ret.Row, Ret.Column + i - 1) & ", " End If Next i hLookupList = Left(RetStr, Len(RetStr) - 2) End Function Application.Volatile True Public Function hLookupList(KeyVal, Vals As Range, Ret As Range) As String Dim i As Long Dim vw As Worksheet Dim rw As Worksheet Dim RetStr As String Application.Volatile True Set vw = Vals.Worksheet Set rw = Ret.Worksheet If Vals.Rows.Count > 1 Then hLookupList = "Too Many Value Rows Selected!" Exit Function End If If Ret.Rows.Count > 1 Then hLookupList = "Too Many Return Rows Selected!" Exit Function End If If Vals.Columns.Count <> Ret.Columns.Count Then hLookupList = "Value Range and Return Range must be the same size!" Exit Function End If For i = Vals.Column To Vals.Column + Vals.Columns.Count - 1 If vw.Cells(Vals.Row, i) = KeyVal Then RetStr = RetStr & rw.Cells(Ret.Row, Ret.Column + i - 1) & ", " End If Next i hLookupList = Left(RetStr, Len(RetStr) - 2) End Function Uscita Funzione Public Function hLookupList(KeyVal, Vals As Range, Ret As Range) As String Dim i As Long Dim vw As Worksheet Dim rw As Worksheet Dim RetStr As String Application.Volatile True Set vw = Vals.Worksheet Set rw = Ret.Worksheet If Vals.Rows.Count > 1 Then hLookupList = "Too Many Value Rows Selected!" Exit Function End If If Ret.Rows.Count > 1 Then hLookupList = "Too Many Return Rows Selected!" Exit Function End If If Vals.Columns.Count <> Ret.Columns.Count Then hLookupList = "Value Range and Return Range must be the same size!" Exit Function End If For i = Vals.Column To Vals.Column + Vals.Columns.Count - 1 If vw.Cells(Vals.Row, i) = KeyVal Then RetStr = RetStr & rw.Cells(Ret.Row, Ret.Column + i - 1) & ", " End If Next i hLookupList = Left(RetStr, Len(RetStr) - 2) End Function Uscita Funzione Public Function hLookupList(KeyVal, Vals As Range, Ret As Range) As String Dim i As Long Dim vw As Worksheet Dim rw As Worksheet Dim RetStr As String Application.Volatile True Set vw = Vals.Worksheet Set rw = Ret.Worksheet If Vals.Rows.Count > 1 Then hLookupList = "Too Many Value Rows Selected!" Exit Function End If If Ret.Rows.Count > 1 Then hLookupList = "Too Many Return Rows Selected!" Exit Function End If If Vals.Columns.Count <> Ret.Columns.Count Then hLookupList = "Value Range and Return Range must be the same size!" Exit Function End If For i = Vals.Column To Vals.Column + Vals.Columns.Count - 1 If vw.Cells(Vals.Row, i) = KeyVal Then RetStr = RetStr & rw.Cells(Ret.Row, Ret.Column + i - 1) & ", " End If Next i hLookupList = Left(RetStr, Len(RetStr) - 2) End Function Uscita Funzione Public Function hLookupList(KeyVal, Vals As Range, Ret As Range) As String Dim i As Long Dim vw As Worksheet Dim rw As Worksheet Dim RetStr As String Application.Volatile True Set vw = Vals.Worksheet Set rw = Ret.Worksheet If Vals.Rows.Count > 1 Then hLookupList = "Too Many Value Rows Selected!" Exit Function End If If Ret.Rows.Count > 1 Then hLookupList = "Too Many Return Rows Selected!" Exit Function End If If Vals.Columns.Count <> Ret.Columns.Count Then hLookupList = "Value Range and Return Range must be the same size!" Exit Function End If For i = Vals.Column To Vals.Column + Vals.Columns.Count - 1 If vw.Cells(Vals.Row, i) = KeyVal Then RetStr = RetStr & rw.Cells(Ret.Row, Ret.Column + i - 1) & ", " End If Next i hLookupList = Left(RetStr, Len(RetStr) - 2) End Function

Poi:
Inserisci questo nella cella where vuoi la tua list: =hLookupList(BF17, $AM$16:$BB$16, $AM$17:$BB$17)

Purtroppo non esiste un modo integrato per fare una vlookup o una function di indice / corrispondenza restituire un'arrays. Potresti farlo con una formula personalizzata o se sai che ci sono un numero limitato di risultati, qualche ricerca nidificata. Lewiy a mrexcel.com ha scritto una grande function personalizzata che utilizzo, che si può trovare qui . Questa function può essere lento se si guarda un numero elevato di righe.

Poiché cerchi le colonne e vuoi separare i risultati invece di spazi, devi modificare il codice come segue:

 Function MYVLOOKUP(lookupval, lookuprange As Range, indexcol As Long) Dim r As Range Dim result As String result = "" For Each r In lookuprange If r = lookupval Then result = result & "," & r.offSet(indexcol, 0) End If Next r result = Right(result, Len(result) - 1) MYVLOOKUP = result End Function result = "" Function MYVLOOKUP(lookupval, lookuprange As Range, indexcol As Long) Dim r As Range Dim result As String result = "" For Each r In lookuprange If r = lookupval Then result = result & "," & r.offSet(indexcol, 0) End If Next r result = Right(result, Len(result) - 1) MYVLOOKUP = result End Function Per each r In lookuprange Function MYVLOOKUP(lookupval, lookuprange As Range, indexcol As Long) Dim r As Range Dim result As String result = "" For Each r In lookuprange If r = lookupval Then result = result & "," & r.offSet(indexcol, 0) End If Next r result = Right(result, Len(result) - 1) MYVLOOKUP = result End Function Se r = lookupval allora Function MYVLOOKUP(lookupval, lookuprange As Range, indexcol As Long) Dim r As Range Dim result As String result = "" For Each r In lookuprange If r = lookupval Then result = result & "," & r.offSet(indexcol, 0) End If Next r result = Right(result, Len(result) - 1) MYVLOOKUP = result End Function

La tua formula sarebbe allora = MYVLOOKUP (BF17, AM17: BB17, -1) Se vuoi uno spazio dopo la virgola (nei risultati), cambia:

 result = result & "," & r.offSet(indexcol, 0)

 result = result & ", " & r.offSet(indexcol, 0)

Se non avete ancora utilizzato le funzioni personalizzate, premete Alt + F11 quando in Excel per ricall il VBE e aggiungere un nuovo module alla cartella di lavoro che si sta lavorando (Inserisci -> Modulo). Basta copiare e incollare il codice in esso. Vorrei raccomandare Paste Special -> Valori prima di submit la cartella di lavoro a chiunque. Fathemes sapere se hai delle domande per farlo!