Reputation: 23
I want to show several results with an Index Match formula,
I have this board in A1
And I want to show every number relatives to "Paris in A3 like 1,2,4, 7.
I tried :
={INDEX($A$1:$A$7,MATCH("Paris",$A$A:$B$B;0))}
={INDEX($A$1:$A$7,MATCH("Paris",$A$A:$B$B;0)0)}
={INDEX($A$1:$A$7,MATCH("Paris",$A$A:$B$B);0)}
But it doesn't works and I Don't know why...
Upvotes: 0
Views: 5411
Reputation: 3563
Alternatively, if you have access to TEXTJOIN
formula, you can use the following Array formula (Ctrl+Shift+Enter):
=TEXTJOIN(", ",,IF($B$1:$B$7="Paris",$A$1:$A$7,""))
Upvotes: 3
Reputation: 60494
MATCH
will always match the first instance. You need to generate an array of all the instances that match Paris
, and then use that value in the INDEX
function.
Also, in your posted formulas, you are using both the comma and semicolon as separators. That will also cause your formula to fail. Use only the proper separator for your system settings.
So something like: where C1 contains the city of choice
=IFERROR(INDEX($A$1:$A$7,AGGREGATE(15,6,1/($B$1:$B$7=$C$1)*ROW($A$1:$A$7),ROWS($1:1))),"")
and fill down as far as you need.
Upvotes: 3