Matching Values From A Data Set?

[atelian]

Hi All,

I have a data set where I need to find a specific number within an array and then find the corresponding name. I've inserted the table below. I'm looking to figure out who's weight is 5.8 and have that name get pulled from the "All Weights" table to the "Top Weights" table. The "All Weights" table will be updating all the time so it needs to be able to react to those changes as it goes. Also, if there is a tie for weight, I need to find a way to pull both names into different cells. I've tried a series of iferror vlookups, match formulas, I just can't figure it out. I don't know if I've explained this clearly but hopefully you can help.

Thanks!

Adam

Top Weights
	Name	Weight
#1	TBD	5.8
#2	TBD	5.2
#3	TBD	5.2
All Weights
Name	Weight 1	Weight 2	Weight 3	Weight 4	Weight 5
Adam	1	2	3	4	5.2
Mike	0.5	1.5	2.5	3.5	4.5
John	1.2	2.2	3.2	4.2	5.2
Larry	0.8	1.8	2.8	3.8	4.8
Chriss	1.8	2.8	3.8	4.8	5.8

<tbody>
</tbody>

 

[Special-K99]

With your All weights table in Sheet1!A1:F6, ie Adam is in A2

in A8 put 5.8 then

=INDEX(B1:F1,MAX(IF(B2:F6=A8,COLUMN(B1:F1)-COLUMN(B1)+1)))
returns the column where 5.8 is located, ie Weight 5
=INDEX(A2:A6,MAX(IF(B2:F6=A8,ROW(A2:A6)-ROW(A2)+1)))
returns the row where 5.8 is located, ie Chriss

NOTE: Array formulas, use Ctrl-Shift-Enter
Also that there must be no duplicates in the table for this to work

If you Google Reverse 2-way lookup you may find a solution that allows duplicates