Lookup formula to retrieve data based on specific criteria

Hi,

I have two sheets. In the main sheet I will have entries (ID:s) that are categorised differently. I want to divide the workbook to worksheets according to those categories. Each sheet would only need to contain entries (ID:s) for specific category. I have tried between different lookups but cannot land on optimal solution.

Thanks in advance,
T

Try

B2=IFERROR(INDEX(Main!$A$2:$A$8,AGGREGATE(15,6,ROW(Main!$A$2:$A$8)-1/(Main!$B$2:$B$8=RIGHT(MID(CELL("filename",B1),SEARCH("]",CELL("filename",B1))+1, SEARCH("]",CELL("filename",B1))-SEARCH("[",CELL("filename",B1))-1),1)),ROWS($B$2:B2))),"")

Formula:

Please Login or Register  to view this content.

See the attachment.............

Thanks for feedback please add reputation by clicking star * icon left corner on your thread screen..

Can you upload your actual file with expected result here.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Try in sheet Recurring Monitoring Plan

E4=IFERROR(INDEX('High level Activity Plan'!$E$4:$E$2500,AGGREGATE(15,6,ROW($E$4:$E$2500)-ROW($E$4)+1/('High level Activity Plan'!$F$4:$F$2500=TRIM(SUBSTITUTE(MID(CELL("filename",$B$1),SEARCH("]",CELL("filename",$B$1))+1,LEN(CELL("filename",$B$1))-SEARCH("]",CELL("filename",$B$1))),"Plan",""))),ROWS($E$4:E4))),"")

E4

Formula:

Please Login or Register  to view this content.

Originally Posted by Exceltrouble

I have uploaded now original workbook, hopefully it explains what I want to achieve.

Thanks in advance,
Tanel

It is just because your drop down values are not according your sheet name in your background sheet just change last value with Advice_Support_Inform and it will work.............

Originally Posted by Exceltrouble

Thanks, it does not work for the last sheet advice_support_inform. Works flawlessly for recurring monitoring, non-recurring, and training. Does it take some modification for the last sheet, I still fail to understand the construct of the formula to do it myself

Glad to help you please mark thread as solved and please add reputation by clicking star * icon left corner on your screen which post helps you and happy to help you keep learning