Array problem

[upl365]

Hi everyone!

this is my first post

I have a problem when I use the INDEX&MATCH formula to display columns,

that is, when the column is displayed there are empty cells that I cannot resolve like the image below. I want the result to be like in image B
I really thought hard to find an answer on the internet but no one could solve it

Thanks

 

[ISY]

Hi

=TOCOL(CHOOSECOLS(B5:D10,1),1)

 

[upl365]

Hi

=TOCOL(CHOOSECOLS(B5:D10,1),1)

I don't want to change the search formula, so just change/add some formulas to it

 

[SunnyAlv]

Try this

=OFFSET(INDEX(B4:D9,,MATCH(F3,B3:D3,0)),,,COUNTA(INDEX(B4:D9,,MATCH(F3,B3:D3,0))))

 

[FormR]

Hi, welcome to the forum!

Here's another option you can try:

=LET(CC,CHOOSECOLS(B4:D9,MATCH(F3,B3:D3,0)),FILTER(CC,CC<>""))

 

[Peter_SSs]

Welcome to the MrExcel board!

A couple of other options.

24 01 16.xlsm
	B	C	D	E	F	G	H	I	J
1
2
3	1	2	3		1		a		a
4	a	1	a				b		b
5	b	2	s				c		c
6	c	3	d				d		d
7	d		f
8			f
9			g
Eliminate blanks

Cell Formulas
Range	Formula
H3:H6	H3	=LET(c,INDEX(B4:D9,,MATCH(F3,B3:D3,0)),FILTER(c,c<>"",""))
J3:J6	J3	=LET(c,FILTER(B4:D9,B3:D3=F3),FILTER(c,c<>"",""))
Dynamic array formulas.

 

[upl365]

Work perfectly
Appreciation to everyone who answered, thank you very much everyone

 

[Peter_SSs]

You're welcome. Glad we could help. & thanks for the follow-up.

BTW, I note that you marked post 4 as the solution. While it works for the sample data & therefore may well work for all possible data you might have, just be aware of a couple of things.

1. OFFSET is a volatile function so in my mind, best avoided when other non-volatile options are available, as is the case here.
   
   
2. If it is possible that any of the blank cells might occur within the column of data, that formula will return incorrect results - see example below.

24 01 16.xlsm
	B	C	D	E	F	G	H
1
2
3	1	2	3		1		a
4	a	1	a				0
5		2	s				c
6	c	3	d
7	d		f
8			f
9			g
Eliminate blanks (2)

Cell Formulas
Range	Formula
H3:H5	H3	=OFFSET(INDEX(B4:D9,,MATCH(F3,B3:D3,0)),,,COUNTA(INDEX(B4:D9,,MATCH(F3,B3:D3,0))))
Dynamic array formulas.

 

[upl365]

You're welcome. Glad we could help. & thanks for the follow-up.

BTW, I note that you marked post 4 as the solution. While it works for the sample data & therefore may well work for all possible data you might have, just be aware of a couple of things.

1. OFFSET is a volatile function so in my mind, best avoided when other non-volatile options are available, as is the case here.
   
   
2. If it is possible that any of the blank cells might occur within the column of data, that formula will return incorrect results - see example below.

24 01 16.xlsm
	B	C	D	E	F	G	H
1
2
3	1	2	3		1		a
4	a	1	a				0
5		2	s				c
6	c	3	d
7	d		f
8			f
9			g
Eliminate blanks (2)

Cell Formulas
Range	Formula
H3:H5	H3	=OFFSET(INDEX(B4:D9,,MATCH(F3,B3:D3,0)),,,COUNTA(INDEX(B4:D9,,MATCH(F3,B3:D3,0))))
Dynamic array formulas.

Hi Peter thanks for your reply, I appreciate all the answers but I really appreciate the first answer because he was the first to answer so I marked it as a solution.

For that matter, thanks for the suggestion, I am trying all the formulas that have been given and applying them to my worksheet

 

[Peter_SSs]

I really appreciate the first answer because he was the first to answer so I marked it as a solution.

That's fine. I'm not trying to get you to change the marked solution - just making sure you are aware of potential issues associated with it.

Also note that the very fist suggestion (post 2), if you pop the INDEX/MATCH back in to locate the appropriate column instead of the hard-coded "1", is also a good way.

24 01 16.xlsm
	B	C	D	E	F	G	H
1
2
3	1	2	3		1		a
4	a	1	a				b
5	b	2	s				c
6	c	3	d				d
7	d		f
8			f
9			g
Eliminate blanks (3)

Cell Formulas
Range	Formula
H3:H6	H3	=TOCOL(INDEX(B4:D9,,MATCH(F3,B3:D3,0)),1)
Dynamic array formulas.