Return Row Label and Column Label for Max Value in a Table

supertom37 welcome to the forum.

As a new member you may not be aware of the ability to upload workbooks.

It's encouraged. We can't copy / paste from pictures and some can't upload pics and screen shots due to company policies, firewalls and such. Some browsers can't view them either. It also saves having to retype data you already have.

I went ahead and uploaded my own fake data this time.

In D14:D23 is what you've already figured out.

Formula:

Please Login or Register  to view this content.

The others are array formulas. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

In E14 array entered and filled down

Formula:

Please Login or Register  to view this content.

In F14 array entered and filled down.

Formula:

Please Login or Register  to view this content.

	A	B	C	D	E	F	G	H	I
1
2		Team	3-Mar	4-Mar	5-Mar	6-Mar	7-Mar	8-Mar	9-Mar
3		Yellow	64	85	40	85	24	81	59
4		Black	81	47	81	29	6	66	84
5		Green	12	28	80	15	75	-5	17
6		Orange	41	91	-4	69	84	41	67
7		Red	70	97	78	53	55	45	20
8		White	54	14	59	4	8	-2	90
9		Blue	1	65	97	37	89	4	73
10		Purple	85	56	37	69	74	59	85
11
12
13				Points	Team	Date
14			1st	97	Red	4-Mar
15			2nd	97	Blue	5-Mar
16			3rd	91	Orange	4-Mar
17			4th	90	White	9-Mar
18			5th	89	Blue	7-Mar
19			6th	85	Yellow	4-Mar
20			7th	85	Yellow	6-Mar
21			8th	85	Purple	3-Mar
22			9th	85	Purple	9-Mar
23			10th	84	Black	9-Mar

Hey SuperTom and Dave,

I have another method for doing this problem without using formulas. Because Supertom has Excel 2016 he can use Get & Transform to do all the work. Here are the steps...

1. Open up Dave's attached workbook
2. Select the range from B2 to I10 and leave it selected.
3. Click on the Data Tab -> From Table (it is in the Get & Transform icon group)
4. Click on the box in the "Create Table" dialog to show "My table has headers" and then OK.
5. You will now see the data in the new tool - new to 2016 and add-in for 2010 and 2013
6. Right Click on the work Team (above column 1) and then on "UnPivot Other Columns.
7. Click on the dropdown next to "Value" and then on "Sort Descending"
8. Click on "Close & Load" dropdown and the Close & Load To sub-menu.
9. Click on Existing worksheet and make $H$13 in the "Select a Range"
10. Click on Load

See the answer without ever typing a formula. See the attached with the answer.
PQ Unpivot Answer.xlsx

@ MarvinP,

That's impressive.

Must get add-in.

Hey,

If you install the Power Query Add-In, you will have an inserted step. The Add-In has a tab called Power Query. You will need to click on it after selecting the range. Then find "From Table" on the ribbon. In 2016 all the tools are in a new group called "Get & Transform".

Thanks. I checked that out @ a year ago. Turned out Power Query add-in is only available certain SKUs of 2013; mine isn't one of them.

Time to upgrade?

I'm fighting it. Reports on the desktop version(s) suggest they aren't much of an upgrade, and I feel uneasy about the subscription route. Still that solution you posted is cool.

With the subscription, I get new features every month or two. I open any office app and click on Account and check for updates.