I have an excel file. Its purpose is to have a list of rooms (working rooms), and people names, so I can know TWO things:

1. Who is embedded in each room.
2. What is the room of a specific person.

The file has 2 spreadsheets in it.

The first one (called “people”) is a list of names, with another column which says what is the room of each person. Note: There are NOT two identical names. Every name is different.

The second one (called “rooms”) is a list of 5 columns:

• the first column is the room number
• the other 4, are 4 names of people (because each room has 4 people in it). Note: the order of the people in the room (in the 4 columns) is not important.

The problem starts when I want to swap 2 people from different rooms. Then, I have to change it in both spreadsheets. I want to find a formula that finds a name in “rooms” spreadsheet, and writes the room number (column A) in the correct row in “people” spreadsheet in column B.

I have tried to use one of the lookup formulas (and especially vlookup) but the problem is the fact that those functions search only in the first column and return value from the other columns, whereas I need exactly the opposite: I want to find value (specific name) from one of the 4 columns, and return the value of the first column (the room number - column A). That way I can go to B column in “people” spreadsheet, and insert a formula for each name in it, so it can find the correct room number.

Another problem is the fact that the room numbers in “rooms” spreadsheet are not necessarily sorted. Sometimes I want to take a group and move it to a different room. In this case, all I do is changing the room number in column A, thing that makes the list to be not-sorted.

How can I do this with a formula, in a way that I will need to change only the people in "rooms" spreadsheet, and not to change room number in "people" spreadsheet? Is there a formula that does what I need, or should I use more than one formula to achieve my goal?

CodePudding user response：

Formula in column B is:

=INDEX($B$3:$B$7;SUMPRODUCT(--($C$3:$F$7=A12)*ROW($B$3:$B$7))-2)

Notice the -2 at the end. This is because data starts at row 3 so we need to adjust.

IF we swap 2 names (name 7 and 8) or 2 complete rooms (rooms 1 and 5) the results will autoupdate:

CodePudding user response：

For Excel365; using LET for readability and alterability you can use:

=LET(data, rooms!B4:E8, index, MIN(IF(data=A4, ROW(data) 1))-ROW(INDEX(data,1,1)), INDEX(rooms!A4:A8, index))

Otherwise:

=INDEX(rooms!A4:A8,MIN(IF(rooms!B4:E8=A4,ROW(rooms!B4:E8)-3)))

CodePudding user response：

Using

AGGREGATE(15,6,(ROW(rooms!$B$4:$E$8)-ROW(rooms!$B$4) 1)/(people!$A4=rooms!$B$4:$E$8),1)