Index to return all the vales from row match, in an array

[Rolly_Sefu]

Hello,

I have the following problem, I make a index to return an array of numbers.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]part[/TD]
[TD]price[/TD]
[TD]lookup[/TD]
[TD]result[/TD]
[/TR]
[TR]
[TD]a123[/TD]
[TD]11[/TD]
[TD]a123[/TD]
[TD]=index(B2:B7,Match(C2:C7,A2:A7,0))[/TD]
[/TR]
[TR]
[TD]a124[/TD]
[TD]12[/TD]
[TD]a125[/TD]
[TD]match would return: {1;3;6;#N/A;#N/A;#N/A}[/TD]
[/TR]
[TR]
[TD]a125[/TD]
[TD]13[/TD]
[TD]a128[/TD]
[TD]and index would only return 11[/TD]
[/TR]
[TR]
[TD]a126[/TD]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]a127[/TD]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]a128[/TD]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]a129[/TD]
[TD]17[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

And then get the numbers from index in an array {11;0;13;0;0;16;0} ?

Thanks

 

[Eric W]

Your INDEX formula works fine to return an array with the elements in the order of the lookup values. To see them all, use the Evaluate Formula tool, or select a range when entering the formula, F2:F8 in this example, and use Control+Shift+Enter.

Excel 2012

	A	B	C	D	E	F	G
part	price	lookup	result
a123	a123
a124	a125	match would return: {1;3;6;#N/A;#N/A;#N/A}
a125	a128	and index would only return 11
a126
a127
a128
a129

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]11[/TD]

[TD="align: right"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]11[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]13[/TD]

[TD="align: right"][/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]13[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]16[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]0[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=INDEX(B2:B7,MATCH(C2:C7,A2:A7,0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2:F8[/TH]
[TD="align: left"]{=INDEX(B2:B7,MATCH(C2:C7,A2:A7,0))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2:G8[/TH]
[TD="align: left"]{=IF(ISNUMBER(MATCH(A2:A8,C2:C8,0)),B2:B8,0)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



If you want the array in the order you list at the end of your post, you can use the formula in G2:G8. Maybe the bigger question is what do you want to do with that array when you get it?

 

[Rolly_Sefu]

Hello

This is exactly what I was looking for: {=IF(ISNUMBER(MATCH(A2:A8,C2:C8,0)),B2:B8,0)}

Thank you.

 

[Eric W]

You're welcome.

 

[Rolly_Sefu]

Hy, I got one more question.

{11;0;13;0;0;16;0} starting from here the names for every hit in this array is: {"a123";0;"a125";0;0;"a128";0}

The question. how can I get a value:

find: {"a123";0;"a125";0;0;"a128";0}
lookup in: {0;"a123";0;0;"a125";"a128";0}
return: {0;2;0;0;3;4;0}

The whole point of this is that I want to multiply {11;0;13;0;0;16;0} with {0;2;0;0;3;4;0} but in this case I get a lot of zeros. ( these are 2 different tables )

table 1 {"a123";0;"a125";0;0;"a128";0} = {11;0;13;0;0;16;0}
table 2 {0;"a123";0;0;"a125";"a128";0} = {0;2;0;0;3;4;0}

table 1 * table 2 = {11;0;13;0;0;16;0} * {2;0;3;0;0;4;0}

I can not get the lookup to give me the corect answer.

 

[Rolly_Sefu]

I have managed to create a function that will do exactly what I need, but I think the normal formula would work better

Function test(lookup_search As Range, lookup_result As Range, result As Range)
For Each cell In lookup_search
    Set a = lookup_result.Find(cell, LookIn:=xlValues, lookat:=xlWhole)
    If a Is Nothing Then
        b = 0
    Else
        b = Range(a.Address(False, False)).Offset(0, 1)
    End If
    x = x & b & ";"
Next cell
test = Left(x, Len(x) - 1)
End Function

 

[Rolly_Sefu]

Actually the VBA function works only in a cell, it does NOT help me as an array