Excel - INDEX Function Problem - Expert Solution

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.

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.