Function LookupMultipleValues(gTarget As String, gSearchRange As Range, gColumnNumber As Integer) Dim g As Long Dim k As String For g = 1 To gSearchRange.Columns(1).Cells.Count If gSearchRange.Cells(g, 1) = gTarget Then For J = 1 To g - 1 If gSearchRange.Cells(J, 1) = g...
Function LookupMultipleValues(gTarget As String, gSearchRange As Range, gColumnNumber As Integer) Dim g As Long Dim k As String For g = 1 To gSearchRange.Columns(1).Cells.Count If gSearchRange.Cells(g, 1) = gTarget Then For J = 1 To g - 1 If gSearchRange.Cells(J, 1) = g...
这段代码创建了一个名为 LookupMultipleValues 的用户定义函数。该函数只需要3个参数,使用起来很方便。 Function LookupMultipleValues(gTarget As String, gSearchRange As Range, gColumnNumber As Integer) Dim g As Long Dim k As String For g = 1 To gSearchRange.Columns(1).Cells.Count If gSearchRan...
然后点击OK关闭对话框,保存并关闭代码窗口,返回到工作表,然后输入以下公式:=MultipleLookupNoRept(E2,$A$2:$C$11,3)into a blank cell where you want to output the result, and then drag the fill hanlde down to get all matching values, see screenshot: 请注意:在上式中A2:C11是您要使用的数据...
4。 然后点击OK关闭对话框,保存并关闭代码窗口,返回到工作表,然后输入以下公式:=MultipleLookupNoRept(E2,$A$2:$C$11,3)into a blank cell where you want to output the result, and then drag the fill hanlde down to get all matching values, see screenshot: ...
Finding all matching values using partial match A couple of people have asked in the comments below whether there was a way to use a wildcard, or partial match, for the lookup value in A9. The answer is yes - and here's the formula to use - you can replace the array formula in A11...
For i = 0 To xDic.Count - 1 xStr = xStr & xDic.Keys(i) & "," Next MultipleLookupNoRept = Left(xStr, Len(xStr) - 1) End If End Function https://zh-cn.extendoffice.com/documents/excel/2706-excel-vlookup-return-multiple-values-in-one-cell.html...
2: There might be blank values so COUNTA, FILTER<>"" does not work 3: No VBA allowed Current solutions I have: Solution 1: =XLOOKUP("BrownDog",A:A&B:B,C:C) Disadvantage: A:A&B:Bwill make excel very slow Solution 2: Add an auxiliary column/sheet with so...
Re: Excel: Lookup '1' and return multiple values Hello@pycharge, You can simply use the FILTER() function! Use FILTER(A1:A50,B1:B50=1) Where A1:A50 is the range of item numbers and B1:B50 is the range of the binary values. ...
The lookup_array is the array of values you are trying to find the lookup_value in. The optional match_type determines whether MATCH must find the lookup_value exactly (with a 0), or return the closest match that comes before it (with a 1) or after it (with a -1) alphanumerically....