Stuck on Match function with #N/A; attempting to reverse Index/Match

I am quite good at excel for no training, however, have found myself to hit a wall. I have a 2 way table. Essentially what I am attempting to do is a Index/Match function but in reverse. Where as the Index/match function will look up in two differing directions of arrays and spit out the value of the intersecting point I want to do this in reverse. Example

I have a table where I will search for the maximum values utilizing the =Match(array) function.

Based on the number it finds within the chart/array, i need to find the corresponding row and column identifiers.


For you visual people:

......A.. B. C.. D.. E
......a.. b.. c.. d.. e
A a 10 14 34 42 30
B b 22 31 52 58 10
C c 11 15 13 14 15
D d 16 45 18 19 20
E e 21 22 23 24 25

So in the above example i would use MAX function to return the value of '52'. I then need some way to spit out and identify '52' would be found in row 'C', and 'c' and column 'B' and 'b'. Please note the '.'s are just for spacing purposes. Yes there are two identifying columns/rows for the data set.

I have attempted utilizing Match/Max function to at least identify 2 of the columns and it returns an #N/A no matter what i seem to adjust, =MATCH(MAX(C3:G7),A3:A8&B3:B8,0) where this would identify the first two column numbers in which the max value of 52 is found, but it simply will not work. Under formula evaluation it simply returns a #N/A. All values in the data table are formatted as a number with '0' decimal places. I have attempted this as well, still #N/A, =MATCH(ROUND(MAX(C3:G7),0),A3:A8&B3:B8,0)

If I need to clarify anything further, please let me know.

_____________________

Originally Posted by twiggywales

Lets call your data range "data"

=sumproduct(--(data=max(data))*((column(data)-min(column(data))+1)))

Will give you the column of your max value. When combined with index you can use that to tell you the location

Sorry its a bit unclear

Thank you twiggy, your equation does work out to return the correct column number. I don't fully understand its function and how it works.

Originally Posted by FDibbins

I would do this with a helper row

	A	B	C	D	E	F	G
1		a	b	c	d	e	4 c
2	1	10	14	34	42	30
3	2	22	31	52	58	10
4	3	11	15	13	14	15
5	4	16	45	18	19	20
6	5	21	22	23	24	25
7		22	45	52	58	30

B7=MAX(B2:B6) copied across
G1=INDEX($A$2:$A$6,MATCH(MAX($B$7:$F$7),$B$7:$F$7,0))&" "&INDEX(A1:F1,MATCH(MAX($B$7:$F$7),$B$7:$F$7,0))

@twiggy, based on the above, your suggestion, gives gives 8?
=SUMPRODUCT(--($B$2:$F$7=MAX($B$2:$F$7))*((COLUMN($B$2:$F$7)-MIN(COLUMN($B$2:$F$7))+1)))

Thank you FDibbins, your equation and helper row works well and got me where I needed to get. Much obliged.

Thank you for your effort. I've already utilized FDibbins equation however will review this one and learn.

Done and Done. Automatically overlooked those points thinking it was a signature.