Index/Match over several columns

So on my data sheet "Sheet 1" I have the following....

Teacher	Room	Periods Using Rooms
Joe	300		1A	1B	1C	1D	2A
Jane	352		5A	5C

Now on a separate sheet it it to match the room if they use it that period and return the teachers name.

So for instance it should come out like this on "Sheet 2"....

Room	1A	1B	1C	1D		2A		5A	5C
300	Joe	Joe	Joe	Joe		Joe
352								Jane	Jane

This is what I have
=iferror(if(A2="","",index(Sheet1!A:A,match(A2&B1,Sheet1!B:B&Sheet1!H:I,0))))

I know it wont work because of the H:I piece... not sure really how to replace it (maybe a different formula) so it can search all the columns in the row with that room number to see if there is a match.


*as an added bonus I have to duplicate the formula over several rows and columns on "Sheet 2" ... any way to set up the formula so it will just drag and I don't have to do it manually to each cell? (if that makes sense)

Thanks for your time.

I don't believe this is possible using the index/match combination or any other lookup combination of which I'm aware. My advice is to go one of two routes:

1) Program it in VBA. There are many people who can help with that in this forum if that's what you decide.

2) If VBA is not your thing, I suggest creating a 3rd sheet which combines the room number and the period (for each listed period) from 'Sheet1'. This can be done formulaicly. Then you can use an index/match formula as a way to get the data into 'Sheet2'.

Attach 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.

Originally Posted by FDibbins

Brant, I have found that, in this forum, I try to avoid saying something is not possible in excel, because 1 or 2 of the real geniuses here have proved me wrong lol.

1. I believe this IS possible with formulas maybe with or without helpers.
2. It is also possible to use multiple criteria and criteria ranges in INDEX/MATCH (but not necessarily in this case)

Traggs, is all your data so nicely sequenced and in order?
Can you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

Here is the google sheet so it is easy to share and for you to make a copy.
https://docs.google.com/spreadsheets...it?usp=sharing

The first 4 sheets contain the data. I created "Scrap" as scrap paper to sort out periods. Then the tab "Rooms" is where I need the formulas.

I know this is an Excel forum but I really don't have an option, I have to make this work in Sheets. Sorry if that is an issue, hope we can still make it work.

let me work on it! traggs

copy paste below in rooms sheet at 3 row and 3rd column i.e. in 3C

Formula:

Please Login or Register  to view this content.

then hold ctrl and shift then hit enter to make it array formula ! drag down and drag to right !

if this helps then click add rep button at the bottom left corner of this post

Formula:

Please Login or Register  to view this content.

red part checks for room no in rooms sheet and generates true and false, blue part generates period no from scrap to rooms sheet and generates true and false and their multiplication gives 0's and One's as True*True as 1 and True* False as 0 this gets multiplied by row no when row no holding data gets multiplied by 1 its stays same and rest becomes 0
sumproduct does sum of overall value in which there is only one no and rest are 0's this further checks a condition as if sum is 0 then return empty (" ") else index the row from overall row present in the data

hope this solves your query!

Thanks for the reply

Ok @kuschem I have an additional challenge to this formula.

=IF(SUMPRODUCT(($A2=Scrap!$B$2:$B$1000)*(Rooms!B$1=Scrap!$G$2:$L$1000)*(ROW($2:$1000)))=0,"",IF(SUMPRODUCT(($A2=Scrap!$B$2:$B$1000)*(Rooms!B$1=Scrap!$G$2:$L$1000))>1,SUMPRODUCT(($A2=Scrap!$B$2:$B$1000)*(Rooms!B$1=Scrap!$G$2:$L$1000)),INDEX(Scrap!$A$1:$A$1000,SUMPRODUCT(($A2=Scrap!$B$2:$B$1000)*(Rooms!B$1=Scrap!$G$2:$L$1000)*(ROW($2:$1000))))))

This is what I ended up with which works GREAT!!! What happens is if there is 1 match then it returns a name but >1 match returns a number. Is there a way to add on to this formula in order to make it so that when there is >1 match it will conatenate the names together (maybe with "/" between the names).