How to use Index and Match to retrieve data but exclude some other data

[vjb]

Hello Everyone,

I need help with the attached document. I want that the second tab (Fac Schedule) retrieves data from the first tab (Base Schedule) but distinguish between online and in class courses. The in person courses should be in separate columns and online courses in separate columns although in the original tab they are in the same columns. The classes in person have room numbers (101).
Please find attached the document.

Thank you in advance.

 

[RICH937]

the easiest way to do this is to use a filter. I can show you how to do this, but I'll need to see your base schedule tab as well. Basically, you need to have something that identifies each course by day, time, and format. If in-person and online are in the same format as what you have here, just stacked, you can do something like =Filter(Index([SELECT ENTIRE RANGE IN BASE SCHEDULE],,1),[SELECT ENTIRE B COLUMN IN BASE SCHEDULE TAB]=[SELECT B1 CELL IN FAC TAB]). Make sure to use f4 to add $ in the "entire range in base schedule," but don't add them for the rest of the formula. that should populate an array that corresponds to the in-person Monday data. Then simply drag that formula to the right on the fac tab but change the "1" in the index command to "2" for the second column, 3 for the third and so on. If the data is set up differently in the two tabs, I'll need to see them both along with the row and column identifiers (the 1s and ABCs).

 

[Peter_SSs]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: How to use Index and Match to retrieve data but exlude some other data
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[vjb]

the easiest way to do this is to use a filter. I can show you how to do this, but I'll need to see your base schedule tab as well. Basically, you need to have something that identifies each course by day, time, and format. If in-person and online are in the same format as what you have here, just stacked, you can do something like =Filter(Index([SELECT ENTIRE RANGE IN BASE SCHEDULE],,1),[SELECT ENTIRE B COLUMN IN BASE SCHEDULE TAB]=[SELECT B1 CELL IN FAC TAB]). Make sure to use f4 to add $ in the "entire range in base schedule," but don't add them for the rest of the formula. that should populate an array that corresponds to the in-person Monday data. Then simply drag that formula to the right on the fac tab but change the "1" in the index command to "2" for the second column, 3 for the third and so on. If the data is set up differently in the two tabs, I'll need to see them both along with the row and column identifiers (the 1s and ABCs).

 

[vjb]

Thank you for your reply. I have attached the pic of the base schedule.

 

[vjb]

Thank you for your reply. I have attached the pic of the base schedule.

Up to now I have used this formula to retrieve the information from Base Schedule =IFERROR(INDEX('Base Schedule'!C53:BW53,MATCH(D3,'Base Schedule'!C54:BW54,0)),""). However it doesn't distinguish from Online and In person. Maybe it helps