Index/Match over multiple tabs

And if that were possible, what result would you expect if there is a match on more than one sheet?

Maybe you can step back and explain what you want to achieve instead of fixating on a specific formula.

Ok, good idea. I'll try to explain.

So on tabs "SS", "Math", "Sci", etc i have 3 important columns. Column D has a name, Column E has Room Number, and Column G has Period (period in the form of 1ABCD or 2AC or 8B, for a 4 day rotation).

I want to transform this onto a separate tab that that shows Room Usage.

On that sheet I would have room numbers down the left had side. On the top row i would have 1A 1B 1C 1D 2A 2B etc. I want the teachers names to appear in the columns that match room w period.

ie. on the tab "SS"

D	E	F	G
Joe	300	Math	1AC

On the room usage tab i'd like to see next to 300 the columns with 1A and 1C to have the name Joe in them. The formula, in my mind would search for a match on ALL sheets with Room number and Period and return teacher name if there is a match.

THE BIG ISSUE is i would like the formula to search columns D, E, and G on alllll the tabs.
Also, not sure how I would have it associate "1AC" with "1A" AND "1C".

Hope that helps. You're right, i believe sometimes i get fixated on formulas I know rather than exploring other options.

Originally Posted by teylyn

And if that were possible, what result would you expect if there is a match on more than one sheet?

Maybe you can step back and explain what you want to achieve instead of fixating on a specific formula.

Can you help now??

You are likely to get a better response if you provide us with a sample workbook (not image).so that we do not have to manually key in your data to do any testing.

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.

This looks like a case for Power Query, which can take data from different sheets and transform them into a single table. Without a data sample, this will be impossible to solve, though.

Post a sample file with no more than three data sheets, each with no more than a few rows of data. Then manually create the desired output on a fourth sheet. Then upload that file and explain the logic.

Sorry, this is an Excel forum. Google sheets has formulas that are different from Excel's. The two are not the same. I don't work with Google Sheets.