Question description:
This user has given permission to use the problem statement for this
blog.
I'm trying to put the search and output lists for the following formula on a separate sheet. But simply selecting the data on that sheet won't work. =IFERROR(INDEX(E$2:E$6, AGGREGATE(15, 6, ROW($1:$6)*SIGN(MATCH("*"&D$2:D$6&"*", $A2, 0)), 1)),"")
Solved by X. W. in 31 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
User
15/08/2018 - 07:53
Hi
Excelchat Expert
15/08/2018 - 07:54
hello
Excelchat Expert
15/08/2018 - 07:54
do you have the sheet you are working on?
User
15/08/2018 - 07:55
Yes, but I cannot share it for compliance reasons.
User
15/08/2018 - 07:55
There is nothing else located on the second sheet simply the search and search data in A2:A1001 and the output data in B2:1001.
Excelchat Expert
15/08/2018 - 07:57
can you share a demo?
User
15/08/2018 - 07:57
Sure hang on
Excelchat Expert
15/08/2018 - 07:58
Thanks
User
15/08/2018 - 07:59
There you go
[Uploaded an Excel file]
User
15/08/2018 - 08:00
The search data is on the second sheet, but the formula is still in its very original state
Excelchat Expert
15/08/2018 - 08:03
So you want the Column B operation to move into Sheet2?
User
15/08/2018 - 08:04
I want the lists that the formula uses in column B to be located in Sheet2, the output that's currently in Column B should stay there.
User
15/08/2018 - 08:04
Does that make sense/
User
15/08/2018 - 08:04
?
Excelchat Expert
15/08/2018 - 08:05
I think so, so basically in simple words you need the column B to move into Sheet 2 as it is now
Excelchat Expert
15/08/2018 - 08:05
Or both A and B?
User
15/08/2018 - 08:06
Not exactly
User
15/08/2018 - 08:06
See how the formula in Column B uses the information from D and E 1:6?
Excelchat Expert
15/08/2018 - 08:06
yes
User
15/08/2018 - 08:07
I need the formula to stay exactly where it is but to use instead of D&E 1:6 use Sheet2 A2:1001 and B2:1001
Excelchat Expert
15/08/2018 - 08:08
Oh, now got it
User
15/08/2018 - 08:08
Great :)
User
15/08/2018 - 08:08
Sorry about being so difficult
Excelchat Expert
15/08/2018 - 08:08
wait a bit
User
15/08/2018 - 08:09
Sure
Excelchat Expert
15/08/2018 - 08:13
is it ok?
[Uploaded an Excel file]
User
15/08/2018 - 08:14
Looks good, let me check whether I can transfer it to my workbook
Excelchat Expert
15/08/2018 - 08:16
Sure
User
15/08/2018 - 08:21
Yes, that works! I appears I forgot to fix the ROW
User
15/08/2018 - 08:21
Thank you so much for your help!
Excelchat Expert
15/08/2018 - 08:22
wait a bit, there might be a little fix needed
User
15/08/2018 - 08:22
It seems to work
Excelchat Expert
15/08/2018 - 08:22
here final
[Uploaded an Excel file]
Excelchat Expert
15/08/2018 - 08:23
On my end previously the data seemed to be fetching wrong ones, now fixed! :)
User
15/08/2018 - 08:23
What is different now?
User
15/08/2018 - 08:23
Ah great thank you.
Excelchat Expert
15/08/2018 - 08:24
The row() function might had a wrong reference earlier, now fine
Excelchat Expert
15/08/2018 - 08:24
Most welcome!
User
15/08/2018 - 08:25
Great, have a good night!
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.
