Find Position in Table Based on Table Value

[lwflip]

As the title says, I'm trying to find the position of a value in a table based on a given value.

For instance, consider I have a table of multiple company incomes for different months. I.e., company names in the column position (B1, C1, etc) and months in the row position (A2, A3, etc). In another section of the spreadsheet I use LARGE() to return the 1st, 2nd and 3rd top income values in the data range (in this case B2:C13). I would like a formula that returns the company name and month that correspond to the income values I calculate with the LARGE() function. The actual data I have is unique enough to assume none of my values will be the same (i.e., there will never be 2 data points that correspond to the max value of the range).

[XOR LX]

Hi,

Could you attach a workbook with a few examples and your desired results in each case? It doesn't have to be your actual spreadsheet - a mocked-up, smaller version will suffice, provided it has enough information in to represent your real set-up.

Regards

[Tony Valko]

Try this...


Data Range

	A	B	C	D	E
1		Company1	Company2	Company3	Company4
2	Month1	95	40	34	21
3	Month2	41	22	13	46
4	Month3	64	42	5	90
5	Month4	56	14	83	11
6
7
8	Large1	95	Company1	Month1
9	Large2	90	Company4	Month3
10	Large3	83	Company3	Month4

Enter this array formula** in C8:

=INDEX($1:$1,MAX(IF(B$2:E$5=B8,COLUMN(B2:E5))))

Enter this array formula** D8:

=INDEX(A:A,MAX(IF(B$2:E$5=B8,ROW(B$2:E$5))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Select C8:D8 and copy down as needed.

[lwflip]

Thank you Tony that worked perfectly. Also thanks for being able to decipher my vague description. I'm wrote that out on my phone and that was the best example I could come up with.

[Tony Valko]

You're welcome. Thanks for the feedback!