# Excel - COLUMN Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I have on Sheet1, a table with information Column A (Stores) and Column B (Product). In Sheet2 I want to put in a cell (Store) and underneath all the products of that store. Could help me with a formula, without having to filter by store and copying and pasting.
Solved by F. B. in 29 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 06/09/2018 - 04:19
Welcome to got it pro. We are delighted to have you here with us.
User 06/09/2018 - 04:19
Hi
Excelchat Expert 06/09/2018 - 04:19
I believe I can help.
User 06/09/2018 - 04:19
Thank you
Excelchat Expert 06/09/2018 - 04:20
Would you be able to show me your excel file?
User 06/09/2018 - 04:20
My computer is encrypted and will not let me send files
Excelchat Expert 06/09/2018 - 04:21
Okay I understand.
Excelchat Expert 06/09/2018 - 04:21
I will write some dummy data in the blank spreadsheet on the right.
User 06/09/2018 - 04:21
Ok
User 06/09/2018 - 04:23
Yes, is there a formula that can write only the name of the store and below all the products appear?
Excelchat Expert 06/09/2018 - 04:23
Yeah. I'm currently writing that in sheet2.
User 06/09/2018 - 04:24
I'll wait
Excelchat Expert 06/09/2018 - 04:25
You are using excel right?
User 06/09/2018 - 04:25
Yes
Excelchat Expert 06/09/2018 - 04:26
Thanks for confirming. I'll work on my laptop's excel and share the file with you.
User 06/09/2018 - 04:26
Thank you
Excelchat Expert 06/09/2018 - 04:29
Typically how many rows would there be in your excel file?
User 06/09/2018 - 04:31
50
Excelchat Expert 06/09/2018 - 04:31
Alright, I'm adjusting the formula for 50 rows and sending the file to you.
User 06/09/2018 - 04:32
Thanks
Excelchat Expert 06/09/2018 - 04:33
[Uploaded an Excel file]
Excelchat Expert 06/09/2018 - 04:33
Excelchat Expert 06/09/2018 - 04:33
The formula I wrote in column A sheet 2 is =IFERROR(INDEX(Sheet1!A:A,AGGREGATE(15,3,(Sheet1!\$A\$2:\$A\$51=\$B\$1)/(Sheet1!\$A\$2:\$A\$51=\$B\$1)*ROW(Sheet1!\$A\$2:\$A\$51),ROWS(Sheet1!\$A\$2:A2))),"")
User 06/09/2018 - 04:33
Excelchat Expert 06/09/2018 - 04:34
assuming you would input the store name in Cell B1 of sheet2.
Excelchat Expert 06/09/2018 - 04:34
Column B formula =IFERROR(INDEX(Sheet1!B:B,AGGREGATE(15,3,(Sheet1!\$A\$2:\$A\$51=\$B\$1)/(Sheet1!\$A\$2:\$A\$51=\$B\$1)*ROW(Sheet1!\$A\$2:\$A\$51),ROWS(Sheet1!\$A\$2:A2))),"")
Excelchat Expert 06/09/2018 - 04:34
Please let me know if you have any question.
User 06/09/2018 - 04:35
let me try and let you know
Excelchat Expert 06/09/2018 - 04:35
Sure, take your time.
Excelchat Expert 06/09/2018 - 04:35
Please extend the chat by selecting YES.
Excelchat Expert 06/09/2018 - 04:35
Otherwise the chat will end in 3 minutes.
User 06/09/2018 - 04:36
and if it is greater than 50 rows?
Excelchat Expert 06/09/2018 - 04:37
Do you see the number 50 in the formula? Change it to your need.
Excelchat Expert 06/09/2018 - 04:38
Sorry I actually wrote 51 in the formula, so you need to change it if you have more rows.
User 06/09/2018 - 04:38
Ok
Excelchat Expert 06/09/2018 - 04:39
The reason I didn't accommodate more rows in the formula already is, this formula is pretty complex and adding more rows would make the file slower unnecessarily.
Excelchat Expert 06/09/2018 - 04:39
Makes sense?
User 06/09/2018 - 04:40
Yes
Excelchat Expert 06/09/2018 - 04:40
Great.
Excelchat Expert 06/09/2018 - 04:40
I'm here if you have more questions.
Excelchat Expert 06/09/2018 - 04:40
I mean more question regarding this particular problem. not another problem. :)
User 06/09/2018 - 04:45
It served me in a fantastic way, thanks for your help
Excelchat Expert 06/09/2018 - 04:45
I'm really glad to hear that.
Excelchat Expert 06/09/2018 - 04:46
I would be grateful if you can manage to rate me 5 star for my service.
User 06/09/2018 - 04:46
I replaced it with my data and it worked without problem
User 06/09/2018 - 04:46
Without a doubt
Excelchat Expert 06/09/2018 - 04:46
:) its like a magic, ain't it True!
Excelchat Expert 06/09/2018 - 04:47
Thanks for your patience. If you don't have any question, I would like to go. :)
Excelchat Expert 06/09/2018 - 04:47
Don't forget to visit us today and have a great day ahead!

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

## Click here to get your free Excelchat help session

Trusted by people who work at