How To Populate Name From Non Continuous Range?

[xlmaniac]

Dear All,

I have got 3 tables with employee name & their score across A1:H4 as follows & I would like to populate the bottom 3 scores along with their names.(J1:K4)

To do this, I have named the ranges B2:B4, E2:E4 & H2:H4 as Score.

Then I have put the formulae across K2:K4 as follows:-

K2=SMALL(Score,1), K3=SMALL(Score,2) & K4=SMALL(Score,3)

Pls help with the formulae across J2:J4 which can yield the desired result. In case of any tie with the score, it should populate the names and put across J2:J4.

Emp Name(A1)	% Score(B1)		Emp Name(D1)	% Score(E1)		Emp Name(G1)	% Score(H1)		Emp Name(J1)	Bottom 3 Score(K1)
a	61%		d	63%		g	67%		i	56%
b	65%		e	58%		h	69%		e	58%
c	66%		f	80%		i	56%		a	61%

 

[jorismoerings]

Hi,

try this:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Emp Name(A1)	% Score(B1)		Emp Name(D1)	% Score(E1)		Emp Name(G1)	% Score(H1)		Emp Name(J1)	Bottom 3 Score(K1)
2	a	61%		d	63%		g	67%		i	56%
3	b	65%		e	58%		h	69%		e	58%
4	c	66%		f	80%		i	56%		a	61%
Sheet3

Cell Formulas
Range	Formula
J2	J2	=OFFSET(A1,(SUMPRODUCT((A2:H4=SMALL(A2:H4,1))*ROW(A2:H4))-ROW(A2:H4))+1,(SUMPRODUCT(($A$2:$H$4=SMALL($A$2:$H$4,1))*COLUMN($A$2:$H$4))-COLUMN($A$2:$H$4)+1)-2)
K2	K2	=OFFSET(A1,(SUMPRODUCT((A2:H4=SMALL(A2:H4,1))*ROW(A2:H4))-ROW(A2:H4))+1,(SUMPRODUCT(($A$2:$H$4=SMALL($A$2:$H$4,1))*COLUMN($A$2:$H$4))-COLUMN($A$2:$H$4)+1)-1)
J3	J3	=OFFSET(A1,(SUMPRODUCT((A2:H4=SMALL(A2:H4,2))*ROW(A2:H4))-ROW(A2:H4))+1,(SUMPRODUCT(($A$2:$H$4=SMALL($A$2:$H$4,2))*COLUMN($A$2:$H$4))-COLUMN($A$2:$H$4)+1)-2)
K3	K3	=OFFSET(A1,(SUMPRODUCT((A2:H4=SMALL(A2:H4,2))*ROW(A2:H4))-ROW(A2:H4))+1,(SUMPRODUCT(($A$2:$H$4=SMALL($A$2:$H$4,2))*COLUMN($A$2:$H$4))-COLUMN($A$2:$H$4)+1)-1)
J4	J4	=OFFSET(A1,(SUMPRODUCT((A2:H4=SMALL(A2:H4,3))*ROW(A2:H4))-ROW(A2:H4))+1,(SUMPRODUCT(($A$2:$H$4=SMALL($A$2:$H$4,3))*COLUMN($A$2:$H$4))-COLUMN($A$2:$H$4)+1)-2)
K4	K4	=OFFSET(A1,(SUMPRODUCT((A2:H4=SMALL(A2:H4,3))*ROW(A2:H4))-ROW(A2:H4))+1,(SUMPRODUCT(($A$2:$H$4=SMALL($A$2:$H$4,3))*COLUMN($A$2:$H$4))-COLUMN($A$2:$H$4)+1)-1)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Marcelo Branco]

Try something like this

Pasta2
	A	B	C	D	E	F	G	H	I	J	K
1	Emp Name	% Score		Emp Name	% Score		Emp Name	% Score		Emp Name	Bottom 3 Score
2	a	61%		d	63%		g	67%		i	56%
3	b	65%		e	58%		h	69%		e	58%
4	c	66%		f	80%		i	56%		a	61%
5
Plan10

Cell Formulas
Range	Formula
J2:J4	J2	=INDEX(A$2:H$4,AGGREGATE(15,6,(ROW(A$2:H$4)-ROW(A$2)+1)/(A$2:H$4=K2),COUNTIF(K$2:K2,K2)),AGGREGATE(15,6,(COLUMN(A$2:H$4)-COLUMN(A$2))/(A$2:H$4=K2),COUNTIF(K$2:K2,K2)))
K2:K4	K2	=SMALL($A$2:$H$4,ROWS(K$2:K2))

M.

 

[xlmaniac]

Thank you both for the mesmerising solution.
Really appreciate your help.

 

[Fluff]

A word of warning, if you could have two (or more) people with the same score, then the formula from @jorismoerings could either give the wrong result, or fail

 

[Peter_SSs]

A word of warning, if you could have two (or more) people with the same score, then the formula from @jorismoerings could either give the wrong result, or fail

.. and we are told that is possible:

In case of any tie with the score,