Select a name that is not matched with another cell

[chinglnc]

1	A	B	C	D	E	F
2	List:	Amy	John	Mila	Paul
3
4		Amy (Variable)		Name 1	Name 2	Name 3
5	Subject 1	90%
6	Subject 2	80%
7	Subject 3	70%

border-width:5px;
<tbody>
</tbody>

Dear all

Not sure what is the best way to explain this- basically I need Name 1, 2, 3 to select the names from [B2 to F2], excluding the name that matches with B4 (because that is a variable).

For example, Name 1, 2 and 3 should be John, Mila or Paul (in any order).

And when B4 is changed to John, Name 1, 2 and 3 should be Amy, Mila and Paul.

Any advice?

 

[AlphaFrog]

This is a multi-cell array formula. Edit this formula for the three result cells (D4:F4) as one.

• Select the three result cells D4:F4
• Paste this formula in the formula bar =INDEX($B$2:$E$2,SMALL(IF($B$2:$E$2<>$B$4,COLUMN(B2:E2)-1),COLUMN(2:2)))
• Confirm with Ctrl+Shift+Enter

 

[chinglnc]

Hi AlphaFrog

Thanks for this- the formula works but I just need a little more help with the explanation to make it more applicable for my work:

=INDEX($B$2:$E$2,SMALL(IF($B$2:$E$2<>$B$4,COLUMN(B2:E2)-1),COLUMN(2:2)))

=INDEX(ListArray, SMALL(IF(ListArray<>TheCellToCompare, COLUMN(ListArray), COLUMN(This is where I got confused)

Can I ask how does Column(2:2) works? What does it means?

 

[AlphaFrog]

COLUMN(2:2) is a method to get an array of sequential numbers (1,2,3,4...) for an array formula.

Explanation:

COLUMN(A2) would return the column number of cell A2 which is the value 1

In an array formula, COLUMN(2:2) returns an array of column numbers for each cell in row 2:2 which is an array of sequential numbers 1,2,3,4....

Your multi-cell array formulas use the sequential numbers as the K argument for the SMALL function to return the 1st smallest, 2nd smallest, 3rd smallest... etc.

Does that help?


On another note, be aware of the purpose of the -1 in this part of the formula.
IF($B$2:$E$2<>$B$4,COLUMN(B2:E2)-1)

The -1 is a column# offset for your List array.

The INDEX function is expecting a number from 1 to Count-of-list-array (4 in this example) to return the Name in the List Array.

COLUMN(B2:E2) returns an array (2,3,4,5). So the -1 is subtracted from each column number to make the array (1,2,3,4) which is compatible with the INDEX function.

 

[chinglnc]

That really helps!! thanks for the explanation!