Get all matches in excel

[Rahulwork]

Hi everyone

i have one excel sheet as below and need our help. please refer below example:

I have data in A1:B4. I want the same in as desire as D1:f2

I want to all the possible match in rows format. please help.

	A	B	C	D	E	F
1	Australia	One		Australia	Japan	china
2	Japan	One		One	One	One
3	Australia	Two		Two
4	china	One

 

[shaowu459]

MrExcel.xlsx
	A	B	C	D	E	F
1	Australia	One		Australia	Japan	china
2	Japan	One		One	One	One
3	Australia	Two		Two
4	china	One
Sheet1

Cell Formulas
Range	Formula
D2:F4	D2	=IFERROR(INDEX($B:$B,SMALL(IF($A$1:$A$4=D$1,ROW($1:$4)),ROW(A1))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[shaowu459]

Upload a workbook for your reference, please see if you can open below file:
OneDrive shared file

If you are using Office 365, FILTER function is much easier:

MrExcel.xlsx
	A	B	C	D	E	F
1	Australia	One		Australia	Japan	china
2	Japan	One		One	One	One
3	Australia	Two		Two
4	china	One
Sheet1

Cell Formulas
Range	Formula
D2:D3,E2:F2	D2	=FILTER($B:$B,$A:$A=D1)
Dynamic array formulas.

 

[Rahulwork]

No it's not working. it flle u have used filter function but this is not avaialble with me. dont know this index function not giving medesire value

 

[shaowu459]

No it's not working. it flle u have used filter function but this is not avaialble with me. dont know this index function not giving medesire value

have you pressed CTRL+SHIFT+ENTER for the array formula? can you open the linked file in post #3?

 

[Tom.Jones]

have you pressed CTRL+SHIFT+ENTER for the array formula? can you open the linked file in post #3?

It is not necessary to press Ctrl + Shift + Enter in Office 365. Enter is enough.

 

[shaowu459]

It is not necessary to press Ctrl + Shift + Enter in Office 365. Enter is enough.

Yes, press Enter to finish is enough in 365. i am not sure his excel version, so for the formula using index &small i pressed CSE in case he is not using 365 and just Enter for the formula using Filter function.

 

[Fluff]

It is not necessary to press Ctrl + Shift + Enter in Office 365. Enter is enough.

That is only the case if the OP has dynamic arrays, which not all 365 users have. As the OP has already stated they don't have the Filter function, then CSE is needed.

 

[Fluff]

@Rahulwork
Please edit your account details to show what version of Excel you are using, as this determines what functions you have available. Thanks

 

[Aladin Akyurek]

Hi everyone

i have one excel sheet as below and need our help. please refer below example:

I have data in A1:B4. I want the same in as desire as D1:f2

I want to all the possible match in rows format. please help.

[…]

Australia	One		Australia	Japan	china
Japan	One		One	One	One
Australia	Two		Two
china	One

In D2 control+shift+enter, not just enter, copy across, and down:

=IFERROR(INDEX($B$1:$B$4,SMALL(IF($A$1:$A$4=D$1,ROW($A$1:$B$4)-ROW(INDEX($A$1:$B$4,1,1))+1),ROWS(D$2:D2))),"")

Note. This proposal is robust in picking out the result values.