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

1. ## 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.

2. ## Re: Stuck on Match function with #N/A; attempting to reverse Index/Match

The problem with that is the way match works on the array of data.

If you know its only going to be one value (max) then you can use sumproduct and row() or column() to do it.

I have several examples however as I am on a noble phone I cant upload anything atm

3. ## Re: Stuck on Match function with #N/A; attempting to reverse Index/Match

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

4. ## Re: Stuck on Match function with #N/A; attempting to reverse Index/Match

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)))

5. ## Re: Stuck on Match function with #N/A; attempting to reverse Index/Match

Probably a typo somewhere like I said I on a phone it should read

=SUMPRODUCT(--(B2:F6=MAX(B2:F6))*((COLUMN(B2:F6)-MIN(COLUMN(B2:F6)))+1))

which should give 4 do same with row and then index and combine

6. ## Re: Stuck on Match function with #N/A; attempting to reverse Index/Match

Yeah I think there is a bracket in the wrong place on original post after min should be )))+1)) not ))+1))) lol

7. ## Re: Stuck on Match function with #N/A; attempting to reverse Index/Match

_____________________

8. ## Re: Stuck on Match function with #N/A; attempting to reverse Index/Match

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.

9. ## Re: Stuck on Match function with #N/A; attempting to reverse Index/Match

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

10. ## Re: Stuck on Match function with #N/A; attempting to reverse Index/Match

you'r welcome. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below my 1st post to you)

11. ## Re: Stuck on Match function with #N/A; attempting to reverse Index/Match

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

12. ## Re: Stuck on Match function with #N/A; attempting to reverse Index/Match

Thanks for the feedback

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1