CODEWITHSUNDEEP
excelexcel-formulaexcel-2016
Bitoku
Bitoku

Reputation: 23

Index Match with several results

I want to show several results with an Index Match formula,

I have this board in A1

enter image description here

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

Answers (2)

Justyna MK
Justyna MK

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,""))

enter image description here

Upvotes: 3

Ron Rosenfeld
Ron Rosenfeld

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.

enter image description here

Upvotes: 3

Related Questions