CODEWITHSUNDEEP
excel
KGB91
KGB91

Reputation: 679

Find column value Excel

I have a data that looks like this in Excel:

A   17  8,5 5,666666667 4,25    3,4 2,833333333 2,428571429
B   5   2,5 1,666666667 1,25    1   0,833333333 0,714285714
C   5   2,5 1,666666667 1,25    1   0,833333333 0,714285714
G   4   2   1,333333333 1       0,8 0,666666667 0,571428571

I want to find a the letter representing a specific value. For instance, if I search for "17" I want to get the output "A" and if I am looking for 0,8 I want to get "G". Can anyone help me?

(In reality I want to find sort all the values in the data from the largest to the lowest and find the corresponding letter for the value)

Upvotes: 0

Views: 115

Answers (1)

Forward Ed
Forward Ed

Reputation: 9874

=INDEX($A$1:$A$4,AGGREGATE(14,6,ROW($B$1:$H$4)/(L3=$B$1:$H$4),1))

assuming your example data is in the A1:H4 Range

When you have duplicate value that can be found this formula will return the one in the largest row number. If you want the value in the first row number you can change the 14 to 15.

POC

EDIT

Option 1

=INDEX($A$1:$A$21,AGGREGATE(14,6,ROW($B$18:$L$21)/(L3=$B$18:$L$21),1))

Option 2

=INDEX($A$18:$A$21,AGGREGATE(14,6,ROW($B$18:$L$21)/(L3=$B$18:$L$21),1)-ROW($A$18)+1)

POC#2

The problem you had when you moved it was most likely because of your range reference changes. The row number that is returned from the aggregate check has to be a row within the index range. So option one keep the index range large but matches all possible rows from the aggregate results. Option two reduces the range to just your data and adjust the results to subtract the number of rows before your data so its really referring to which row within the index range you want to look at.

Option 3

Thanks to Scott Craner indicating the full column reference is perfectly acceptable outside the aggregate function, you could also use:

=INDEX($A:$A,AGGREGATE(14,6,ROW($B$18:$L$21)/(L3=$B$18:$L$21‌​),1)

Upvotes: 3

Related Questions